Thread: RE: Referencing named attribute in where clause doesn't work with 7.1.2?

The WHERE clause is evaluated before your SELECT list is determined, so the
aliased value cannot be used.
You can put further NOT NULL tests into the subqueries to make sure that
null values are not returned.
Question: why not just join the tables explicitly?  The more usual SQL
approach would be something like:

SELECT article.title_text_key, on_text.text_value AS title_text_value
FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group ON
code.code_group_id = code.id) ON on_text.lang_id = code.id) ON
article.title_text_key = on_text.text_key
WHERE on_text.text_value IS NOT NULL;

or whatever other tests you want.  In this case, you can easily reference
the fields by name.

> -----Original Message-----
> From:    Andreas Joseph Krogh [SMTP:andreak@officenet.no]
> Sent:    Wednesday, August 08, 2001 7:57 AM
> To:    pgsql-sql@postgresql.org
> Subject:    Referencing named attribute in where clause doesn't work
> with 7.1.2?
> 
> Hi, this is my first post to this list so please...
> I have problems getting this query to work, any ideas?
> 
> select article.title_text_key,
> (select on_text.text_value from on_text where
>     on_text.text_key = title_text_key
>     AND NOT title_text_key is NULL
>     AND on_text.lang_id = (select code.id from code, code_group where
>          code.code_group_id = code_group.id
>           AND code_group.description = 'lang' AND code.code_key =
> 'lang.NO'
>           )
>     )
> as title_text_value from article where NOT title_text_value is NULL;
> 
> ERROR:  Attribute 'title_text_value' not found
> 
> Issuing the same query without the where-clause does work tho, but
> return tupples with null in them which I don't want.
> 
> --
> Andreas Joseph Krogh <andreak@officenet.no>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Jeff Eckermann wrote:
> 
> The WHERE clause is evaluated before your SELECT list is determined, so the
> aliased value cannot be used.
> You can put further NOT NULL tests into the subqueries to make sure that
> null values are not returned.
> Question: why not just join the tables explicitly?

:-) Because I'm not too familiar with joins.

> The more usual SQL
> approach would be something like:
> 
> SELECT article.title_text_key, on_text.text_value AS title_text_value
> FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group ON
> code.code_group_id = code.id) ON on_text.lang_id = code.id) ON
> article.title_text_key = on_text.text_key
> WHERE on_text.text_value IS NOT NULL;
> 
> or whatever other tests you want.  In this case, you can easily reference
> the fields by name.

The problem with the query above is that it doesn't include my
"code.code_key='lang.NO'" test.

I rephrased the query as follows:

SELECT article.title_text_key, on_text.text_value AS title_text_value
FROM article INNER JOIN (on_text INNER JOIN (code INNER JOIN code_group
ON
code.code_group_id = code_group.id) ON on_text.lang_id = code.id AND
code_group.description = 'lang' AND code.code_key = 'lang.NO') ON
article.title_text_key = on_text.text_key
WHERE on_text.text_value IS NOT NULL;

And now it works!

Thank you for helping me out.

--
Andreas Joseph Krogh <andreak@officenet.no>