Thread: Referencing a superselect's column by it's declared alias from a subselect fails
Referencing a superselect's column by it's declared alias from a subselect fails
From
Casey Allen Shobe
Date:
In the following statement (which works), if I s/ps."usesysid"/"UID"/ in th= e=20 two innermost subselects (such that it refers to the declared alias rather= =20 than the true column name), it fails. In DB2, it works in similar=20 situations. create view "users" as select ps."usesysid" as "UID", ps."usename" as "Username", ps."passwd" as "Password", coalesce ( ( select cast (ui."info_value" as integer) from only "user_info" as "ui" inner join only "user_info_types" as "uit" on uit."type_id" =3D ui."info_type_id" where uit."info_type" =3D 'Position ID' and uit."info_type_name" =3D 'Default' and ui."user_id" =3D ps."usesysid" ), 0 ) as "Position ID", coalesce ( ( select cast (ui."info_value" as integer) from only "user_info" as "ui" inner join only "user_info_types" as "uit" on uit."type_id" =3D ui."info_type_id" where uit.info_type =3D 'Creator ID' and uit.info_type_name =3D 'Default' and ui."user_id" =3D ps."usesysid" ), 0 ) as "Creator ID" from only "pg_shadow" as "ps" order by "UID"; --=20 Casey Allen Shobe / Network Security Analyst & PHP Developer SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144 cshobe@secureworks.net / http://www.secureworks.net Content is my own and does not necessarily represent my company. Lost Terminal.
Re: Referencing a superselect's column by it's declared alias from a subselect fails
From
Tom Lane
Date:
Casey Allen Shobe <cshobe@secureworks.net> writes: > In the following statement (which works), if I s/ps."usesysid"/"UID"/ in the > two innermost subselects (such that it refers to the declared alias rather > than the true column name), it fails. In DB2, it works in similar > situations. I find that hard to believe. If true, DB2 is broken. An output-column alias should not be visible in expressions for other output columns. There are obvious circularity problems with allowing such a thing. regards, tom lane