Re: Error from Sub-Select, Simple Example.

Ian Harding writes:

> create table one (
> oneid int,
> onevalue text not null);
>
> create table two (
> twoid int,
> twovalue text);
>
> insert into one (oneid, onevalue)
> select 1,
> case when two.twovalue is null then (select twovalue from one where
> oneid = 1)
> else two.twovalue end
> from one left outer join two on one.oneid = two.twoid;
>
> This causes the not null constraint to fire, but if you run the
> subselect by itself, you get
>
> select twovalue from one where oneid = 1;
> ERROR: Attribute "twovalue" not found
>
> Is this a known issue?

It's certainly known, but it's not an issue. ;-)

> I know I should qualify field names where
> confusion might occur, but where did my error go?

"twovalue" gets resolved as a reference to the respective column in table
"two", which appears in the FROM clause of your query. The name space
available to a subquery includes the outer query.

--
Peter Eisentraut peter_e(at)gmx(dot)net