Teradata SQL Test – 5
Next Question is easy and the answer is easier however some of you may find it tricky. We have two tables both have just one column in it.
INPUT:
Tablename: TABLE1 & TABLE2 | Columnname: RDBMS1 & RDBMS2| Value: Teradata
OUTPUT:
Write a generic SQL code which should give required Result. Leave your answer as comment below.
sel tab1.rdbms1 ,tab2.rdbms2 from
(sel t.*, row_number() over (partition by rdbms1 order by rdbms1) as n1 from table1 t ) tab1
left outer join
(sel tt.*, row_number() over (partition by rdbms2 order by rdbms2) as n2 from table2 tt ) tab2
on tab1.n1 =tab2.n2
Select a.RDBMS1,b.RDBMS2
from TABLE1 a
cross join TABLE2 b
group by 1,2
sel distinct a.*,case when b.rdbms2 =” then null else b.rdbms2 end as rdbms2 from
table1 a
join table2 b
on b.rdbms2 is not null
order by rdbms2 desc
select distinct a.rdbms,b.rdbms from table1 a, (select rdbms from table2 union select null from dual) b
Select RDBMS1,RDBMS2
from Table1 Left outer Join Table2
on RDBMS1=RDBMS2
Group by 1,2;
Solution:
Generic query
select A.a,B.b from
(select RDBMS1 as a, row_number() over (order by RDBMS1) rnk from Table1) A
left join
(select RDBMS2 as b, row_number() over (order by RDBMS2) rnk from Table2) B
on A.rnk=B.rnk
Hi,
Suppose i have table1 is having 100 rows and table2 is having 50 rows.then with the case statement you compare 100 values is it possible if tables are having more number of rows?
Hi Poorna
What kind of comparison you intent to do ?
With the help of joins you can compare billions n billions of rows.
Cheers
Raj
SELECT RDBMS1,RDBMS2 FROM (
SELECT RDBMS1,ROW_NUMBER() OVER (PARTITION BY RDBMS1 ORDER BY RDBMS1) RNK,
CASE WHEN RNK=1 THEN RDBMS2 ELSE ‘NULL’ END AS RDBMS2
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2
ON
RDBMS1=RDBMS2
)X
Hi Pravin
Kudos. That’s right. However can you think of another way to get the same result without using any OLAP function ?
Cheers
Raj
select a.val,case cnt when 1 then b.rdbms2 else NULL end as rdbms2
from
(select rdbms1 as val,count(*) cnt from table1 group by 1
union
select rdbms2 as val,count(*) cnt from table2 group by 1)a
left outer join table2 b on a.val = b.rdbms2;