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.