Rows on the fly and an outer join (use a lateral statement?)

[gimme_this_gimme_that]

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.otherdata column and return
nulls when there is no match.

....

This is sort of what I want:

select e.login,e.otherdata from employee e
left outer join (select 'EEEEEE01' from sysibm.sysdummy1
union select 'AAAAAA01' from sysibm.sysdummy1
union select 'BBBBBB01' from sysibm.sysdummy1
) 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.

Thanks.

[gimme_this_gimme_that]

This works, but is there a better way to create the b table?

Thanks.

select e.login,e.otherdata from employee e
left outer join (select 'EEEEEE01' login from sysibm.sysdummy1
union select 'AAAAAA01' login from sysibm.sysdummy1
union select 'BBBBBB01' login from sysibm.sysdummy1
) b
on b.login=e.login

[Serge Rielau]

gi*******************@yahoo.com 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.otherdata column and return
nulls when there is no match.

...

This is sort of what I want:

select e.login,e.otherdata from employee e
left outer join (select 'EEEEEE01' from sysibm.sysdummy1
union select 'AAAAAA01' from sysibm.sysdummy1
union select 'BBBBBB01' from sysibm.sysdummy1
) 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.otherdata 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.otherdata from employee e
left outer join (select 'EEEEEE01' from sysibm.sysdummy1
union all select 'AAAAAA01' from sysibm.sysdummy1
union all select 'BBBBBB01' from sysibm.sysdummy1
) 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/

[gimme_this_gimme_that]

Thanks for all the solutions Serge.

This was SWEET!

select e.login,e.otherdata from employee e
left outer join (VALUES ('EEEEEE01'),
('AAAAAA01'),
('BBBBBB01')) AS B(login)
on b.login=e.login

Serge Rielau wrote:

gi*******************@yahoo.com wrote:

I have an employee table with two columns, one named login, the other

[ChrisC]

gi*******************@yahoo.com wrote:

Thanks for all the solutions Serge.

This was SWEET!

select e.login,e.otherdata from employee e
left outer join (VALUES ('EEEEEE01'),
('AAAAAA01'),
('BBBBBB01')) AS B(login)
on b.login=e.login

I haven't run across that way of doing it before - very nice.

One other option - maybe not as nice - is to use common table
expressions:

with B(login) as (VALUES ('EEEEEE01'),
('AAAAAA01'),
('BBBBBB01'))
select e.login,e.otherdata from employee e
left outer join b on b.login = e.login

As an aside, do these queries actually return what you want? I would
expect you should get the entire employee table with full values as
defined in the employee table. If you define additional logins in the
'B' table, I wouldn't expect them to be returned in this query. Unless
you switched the order (...from b left outer join employee e...).

-Chris

[gimme_this_gimme_that]

Yes, I switched the order, well actually, I changed it to a right outer
join.

I'm getting a bunch of login values from LDAP in a Java program and
then
I'm fetching addition information for the users from a database table.
I didn't
want to use an in statement and execute if statements on the results to
match things up.

As an aside, do these queries actually return what you want? I would
expect you should get the entire employee table with full values as
defined in the employee table. If you define additional logins in the
'B' table, I wouldn't expect them to be returned in this query. Unless
you switched the order (...from b left outer join employee e...).

-Chris