All Forums Database
jsatish2008 3 posts Joined 09/13
21 Oct 2013
Adhoc query request

Hi,

I have the below two tables.I need to get the col2 value from T2 table.
Join is between t1.col3=t2.col1
If we need do Left Outer Join normally we will get only value and 2 null values.
Take the corresponding col1 from T1 for the null value and get the col3 from T1 instead of NULL. i.e. take 123 instead of NULL.
So my output should look like

 

1 x A
1 y A
1 z A

 

CT T1
(
COL1 INTEGER,
COL2 VARCHAR(10),
COL3 INTEGER
)

 

INS INTO T1 VALUES(1,'X',NULL);
INS INTO T1 VALUES(1,'Y',NULL);
INS INTO T1 VALUES(1,'Z',123);

 

CT T2
(
COL1 INTEGER,
COL2 VARCHAR(10)
)

 

INS INTO T2 VALUES(123,'A')

 

Regards,

Satish.

KVB 124 posts Joined 09/12
22 Oct 2013

SEL T1.COL1,T1.COL2,T1.COL3,T2.COL2
FROM
(
SEL  COL1,COL2,MAX(COL3) OVER(PARTITION BY COL1 ORDER BY COL3) COL3 FROM T1
) T1
LEFT OUTER JOIN
T2 ON T1.COL3 =T2.COL1
 

You must sign in to leave a comment.