Help Newbie to DB2

[jules...@my-deja.com]

I have to merge to tables and use the values from the second table if
the date is less than today. The primary key is Emp. Todays date is
2000/12/31.

Table a Table b

Emp Name Address Zip Date Emp Name Address Zip Date
1 Al 10 Elm 111 2000/01/01 2 Tom 15 Ed 555 2000/12/31
2 Tom 11 Brown 333 2000/01/03
3 Stu 13 Ed 444 2000/01/04

I want the resulting join to look like this

Table a

Emp Name Address Zip Date
1 Al 10 Elm 111 2000/01/01
2 Tom 15 Ed 555 2000/12/31
3 Stu 13 Ed 444 2000/01/04

Any help appreciated
Thanks...

Sent via Deja.com
http://www.deja.com/

[Knut Stolze]

On Wed, 13 Dec 2000 04:40:01 GMT, jules...@my-deja.com wrote:
> I have to merge to tables and use the values from the second table if
> the date is less than today. The primary key is Emp. Todays date is
> 2000/12/31.
>
> Table a Table b
>
> Emp Name Address Zip Date Emp Name Address Zip Date
> 1 Al 10 Elm 111 2000/01/01 2 Tom 15 Ed 555 2000/12/31
> 2 Tom 11 Brown 333 2000/01/03
> 3 Stu 13 Ed 444 2000/01/04
>
> I want the resulting join to look like this
>
> Table a
>
> Emp Name Address Zip Date
> 1 Al 10 Elm 111 2000/01/01
> 2 Tom 15 Ed 555 2000/12/31
> 3 Stu 13 Ed 444 2000/01/04

I don't quite understand what you want to do. I'm confused with your use of
the term 'today'. Do you mean the actual date of today? Or do you want to use
rows from table a if the Date value is smaller than the Date value of a row
with the same primary key in table b; otherwise, the row from table b is used?

You could simply use a union of table a & b and only include these rows from
table a where no row with the same PK exists in b and with a larger date.

SELECT Emp, Name, Address, Zip, Date
FROM a
WHERE NOT EXISTS (
SELECT *
FROM b
WHERE a.Emp = b.Emp AND
a.Date <= b.Date )
UNION ALL
SELECT Emp, Name, Address, Zip, Date
FROM b
WHERE NOT EXISTS (
SELECT *
FROM a
WHERE a.Emp = b.Emp AND
a.Date > b.Date );

--
Knut Stolze <sto...@us.ibm.com>
DB2 UDB Spatial Extender
IBM Silicon Valley Lab