gi************* ******@yahoo.co m wrote:
I have an employee table with two columns, one named login, the other
named otherdata.
I have a list of login values, some of which do not exist in the
employee table.
I want to fetch the data in the employee.otherd ata column and return
nulls when there is no match.
...
This is sort of what I want:
select e.login,e.other data from employee e
left outer join (select 'EEEEEE01' from sysibm.sysdummy 1
union select 'AAAAAA01' from sysibm.sysdummy 1
union select 'BBBBBB01' from sysibm.sysdummy 1
) b
on b(1)=e.login
The problem with this statement is that the creation of the b table is
very slow (there are typically 100 login values), that b(1) is a syntax
error (there wasn't a way to assign the column "login" to the union).
Is this a job for a lateral statement?
I'm using DB2 8.2.0.
OK, lets roll this one up backwards, there is a lot to be learned here:
* b(1) Nice try at syntax, but DB2 will look for a
function b(<number>) here. If any b.1 *shudder* would be it.
You want to specify the column list:
... join (.....) as b(c1, c2, ...) on b.c1 = ...
* Alternatively you can name the columns in the select list of the
union.
The column names get inherited if ALL matching columns agree on
the name.
SELECT 1 AS X, 2 AS Z FROM T
UNION ALL
SELECT 3 AS Y, 4 AS Z FROM T
=> (<noname>, "Z")
* You likely want UNION ALL and not UNION. UNION eliminates duplicates.
An expensive undertaking. In many, many cases where UNION is used the
developer "meant" UNION ALL. If I could turn back time I'd fight to
make: UNION == UNION ALL and folks would have to write UNION DISTINCT.
(or disallow UNION without modifier to begin with....)
* DB2 for LUW supports the VALUES clause. That is there is no need to go
after "funny" single row tables or glue together UNION ALLs:
select e.login,e.other data from employee e
left outer join (VALUES ('EEEEEE01'),
('AAAAAA01'),
('BBBBBB01')) AS B(login)
on b.login=e.login
If you need compatibility with DB2 for zOS use:
select e.login,e.other data from employee e
left outer join (select 'EEEEEE01' from sysibm.sysdummy 1
union all select 'AAAAAA01' from sysibm.sysdummy 1
union all select 'BBBBBB01' from sysibm.sysdummy 1
) b(login)
on b(1)=e.login
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/