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@gmx.net