All Forums Database
tshas 1 post Joined 01/15
12 Jan 2015
Teradata - to solve a data issue with query or Procedure

I am new to Teradata. I have to solve below problem using SQL/Stored procedure in Teradata.
Problem - As shown in below input data, We need to link the records in the table by the values of Col1 and Col2.
Logic - For all set of records which are having linking values of col1 and col2 should have min(col1) as col3.
for example row 1-5 are linked by 3160370 and 4856938 values and hence should have 3160370 in col3.
Input data

Col1 Col2
1 3160370 3160370
2 3160370 4856938
3 3160370 11633062
4 4856938 11633063
5 4856938 11633064
6 4271481 4271481
7 4271481 5968049
8 4271481 12744173
9 5968049 12744174
10 5968049 12744175

Output data

Col1 Col2 Col3
1 3160370 3160370 3160370
2 3160370 4856938 3160370
3 3160370 11633062 3160370
4 4856938 11633063 3160370
5 4856938 11633064 3160370
6 4271481 4271481 4271481
7 4271481 5968049 4271481
8 4271481 12744173 4271481
9 5968049 12744174 4271481
10 5968049 12744175 4271481

Please let me know if more details are needed to solve this problem.
Please help!

ST
dnoeth 4628 posts Joined 11/04
12 Jan 2015

This is a simple task for recursion:

WITH RECURSIVE cte (Col1, Col2, minCol) AS
  (
   SELECT Col1, Col2, Col1 AS minCol
   FROM   tab
   WHERE  Col1 = Col2

   UNION ALL 

   SELECT vt.Col1, vt.Col2, cte.minCol
   FROM   cte JOIN tab
   ON  cte.Col2 = vt.Col1
   AND vt.Col1 <> vt.Col2
 )
SELECT * FROM cte

 

Of course it's more complicated if there are any loops in your data...

Dieter

You must sign in to leave a comment.