Outer Join and Sort.

DB2begin · #1 (**permalink**) 05-10-10, 22:22

I have been learning a lot by posting and going through various posts. Thanks for all your help everyone.

Here is my question with sample data.

TableA

HTML Code:

CD    VALUE   CTGRY     ID       EFCTV-DT     START-DT       STop-dt

05     EE         H      3        03/15/2009   01/12/2009      NULL
11     AA         Z      1        07/15/2009   07/15/2009      null
15     JJ         Z      3        03/15/2009   01/12/2009       NULL
12     BB         Z      5        03/23/2006   03/16/2009      07/15/2009

CTGRY

Code:

CTG   DESCRIPTION
Z        I like letter Z
H        I like letter H

TABLEB

Code:

CD   CTG   ID   EFCTV_DT     sTART-DT    STOP-DT
12    Z       3   05/12/2009  05/12/2009   NULL.

Result Table should look like below.

Code:

cD   VALUE   CTG      ID   TABLE-A.EFCTV-DT    TABLE-B.EFCTV-DT

05     EE       Z         3      03/15/2009              05/12/2009
11     JJ       H          3      03/15/2009                -----

Search criteria

WHERE ID = 3
AND EFCTV_DT = 03/15/2009.

AND TABLE-A.CTG = CTGRY.CTG

ORDER BY CTGRY.DESCRIPTION descending.