pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Eckermann <jeckermann@verio.net>
To: 'Andreas Joseph Krogh' <andreak@officenet.no>
To: pgsql-sql@postgresql.org
Subject: RE: Referencing named attribute in where clause doesn't work with 7.1.2?
Date: Wed, 8 Aug 2001 08:49:04 -0500
Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094B8D@NEZU> (raw)

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



view thread (7+ messages)  latest in thread

Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094B8D@NEZU>
Permalink:  ../08CD1781F85AD4118E0800A0C9B8580B094B8D@NEZU/
Also on:    postgresql.org/message-id/08CD1781F85AD4118E0800A0C9B8580B094B8D@NEZU

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jeckermann@verio.net, andreak@officenet.no
  Subject: RE: Referencing named attribute in where clause doesn't work with 7.1.2?
  In-Reply-To: <08CD1781F85AD4118E0800A0C9B8580B094B8D@NEZU>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox