Adhoc query request

Database

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.

1 REPLY
KVB
Enthusiast

Re: Adhoc query request

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