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