Teradata - to solve a data issue with query or Procedure

Database
N/A

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!

1 REPLY
Senior Apprentice

Re: Teradata - to solve a data issue with query or Procedure

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...