pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Referencing named attribute in where clause doesn't work with 7.1.2?
7+ messages / 4 participants
[nested] [flat]

* Referencing named attribute in where clause doesn't work with 7.1.2?
@ 2001-08-08 12:57 Andreas Joseph Krogh <andreak@officenet.no>
  2001-08-08 13:14 ` Re: Referencing named attribute in where clause doesn't work with 7.1.2? Thomas Good <tomg@admin.nrnet.org>
  2001-08-08 13:42 ` Re: Referencing named attribute in where clause doesn't work with 7.1.2? Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 2 replies; 7+ messages in thread

From: Andreas Joseph Krogh @ 2001-08-08 12:57 UTC (permalink / raw)
  To: pgsql-sql

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>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Referencing named attribute in where clause doesn't work with 7.1.2?
  2001-08-08 12:57 Referencing named attribute in where clause doesn't work with 7.1.2? Andreas Joseph Krogh <andreak@officenet.no>
@ 2001-08-08 13:14 ` Thomas Good <tomg@admin.nrnet.org>
  2001-08-08 13:22   ` Re: Referencing named attribute in where clause doesn't workwith 7.1.2? Andreas Joseph Krogh <andreak@officenet.no>
  1 sibling, 1 reply; 7+ messages in thread

From: Thomas Good @ 2001-08-08 13:14 UTC (permalink / raw)
  To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql

On Wed, 8 Aug 2001, Andreas Joseph Krogh wrote:

> 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

Hallo Andreas,

Relocating your AS clause to the topmost string?  BTW, table aliasing
would make for less typing.

Best of luck!

> 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
> 


--------------------------------------------------------------------
               SVCMC - Center for Behavioral Health                  
--------------------------------------------------------------------
Thomas Good                          tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst                              Phone:  718-354-5528 
Residential Services                            Mobile: 917-282-7359  
--------------------------------------------------------------------
/*           Die Wahrheit Ist Irgendwo Da Draussen...             */
--------------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Referencing named attribute in where clause doesn't workwith 7.1.2?
  2001-08-08 12:57 Referencing named attribute in where clause doesn't work with 7.1.2? Andreas Joseph Krogh <andreak@officenet.no>
  2001-08-08 13:14 ` Re: Referencing named attribute in where clause doesn't work with 7.1.2? Thomas Good <tomg@admin.nrnet.org>
@ 2001-08-08 13:22   ` Andreas Joseph Krogh <andreak@officenet.no>
  0 siblings, 0 replies; 7+ messages in thread

From: Andreas Joseph Krogh @ 2001-08-08 13:22 UTC (permalink / raw)
  To: pgsql-sql

Thomas Good wrote:
> 
> On Wed, 8 Aug 2001, Andreas Joseph Krogh wrote:
> 
> > 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
> 
> Hallo Andreas,
> 
> Relocating your AS clause to the topmost string?  BTW, table aliasing
> would make for less typing.
> 
> Best of luck!

I don't get it, what do you mean by "Relocating your AS clause to the
topmost string"?

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Referencing named attribute in where clause doesn't work with 7.1.2?
  2001-08-08 12:57 Referencing named attribute in where clause doesn't work with 7.1.2? Andreas Joseph Krogh <andreak@officenet.no>
@ 2001-08-08 13:42 ` Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 0 replies; 7+ messages in thread

From: Peter Eisentraut @ 2001-08-08 13:42 UTC (permalink / raw)
  To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql

Andreas Joseph Krogh writes:

> 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

The select list is not in scope for the where clause.  Basically, the
order of processing and approximately the nesting of scopes is:

from -> where -> group -> having -> select -> order

Any names generated by an element on the right cannot be in scope in
elements on the left.  (In some cases it might work as a convenience
anyway, but not in your case.)

I think you might be better off writing this query as a proper join
anyway.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: Referencing named attribute in where clause doesn't work with 7.1.2?
@ 2001-08-08 13:49 Jeff Eckermann <jeckermann@verio.net>
  2001-08-08 14:41 ` Re: RE: Referencing named attribute in where clause doesn't work with7.1.2? Andreas Joseph Krogh <andreak@officenet.no>
  0 siblings, 1 reply; 7+ messages in thread

From: Jeff Eckermann @ 2001-08-08 13:49 UTC (permalink / raw)
  To: 'Andreas Joseph Krogh' <andreak@officenet.no>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: RE: Referencing named attribute in where clause doesn't work with7.1.2?
  2001-08-08 13:49 RE: Referencing named attribute in where clause doesn't work with 7.1.2? Jeff Eckermann <jeckermann@verio.net>
@ 2001-08-08 14:41 ` Andreas Joseph Krogh <andreak@officenet.no>
  0 siblings, 0 replies; 7+ messages in thread

From: Andreas Joseph Krogh @ 2001-08-08 14:41 UTC (permalink / raw)
  To: pgsql-sql

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>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: RE: Referencing named attribute in where clause doesn't work with7.1.2?
@ 2001-08-08 16:08 Jeff Eckermann <jeckermann@verio.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Jeff Eckermann @ 2001-08-08 16:08 UTC (permalink / raw)
  To: 'Andreas Joseph Krogh' <andreak@officenet.no>; pgsql-sql

I suggest the best place to put your tests is in the WHERE clause.  They are
out of place in the JOIN clauses, even though this (apparently) works as you
expect, because they do not represent a joining of fields.  Also, easier to
read and understand in the WHERE clause.
On joins in general: there are plenty of kinds, but in real life inner or
outer (left or right: all the same) joins will be all you want 99% of the
time.  Read up on what those do, and you'll be in good shape.

> -----Original Message-----
> From:	Andreas Joseph Krogh [SMTP:andreak@officenet.no]
> Sent:	Wednesday, August 08, 2001 9:41 AM
> To:	pgsql-sql@postgresql.org
> Subject:	Re: RE: Referencing named attribute in where clause doesn't
> work  with7.1.2?
> 
> 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>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2001-08-08 16:08 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-08 12:57 Referencing named attribute in where clause doesn't work with 7.1.2? Andreas Joseph Krogh <andreak@officenet.no>
2001-08-08 13:14 ` Thomas Good <tomg@admin.nrnet.org>
2001-08-08 13:22   ` Re: Referencing named attribute in where clause doesn't workwith 7.1.2? Andreas Joseph Krogh <andreak@officenet.no>
2001-08-08 13:42 ` Peter Eisentraut <peter_e@gmx.net>
2001-08-08 13:49 RE: Referencing named attribute in where clause doesn't work with 7.1.2? Jeff Eckermann <jeckermann@verio.net>
2001-08-08 14:41 ` Re: RE: Referencing named attribute in where clause doesn't work with7.1.2? Andreas Joseph Krogh <andreak@officenet.no>
2001-08-08 16:08 RE: RE: Referencing named attribute in where clause doesn't work with7.1.2? Jeff Eckermann <jeckermann@verio.net>

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