Recursive Updation in Query

Database
Enthusiast

Recursive Updation in Query

I'm stuck with the Query as described below:

I've a table (T1) with following data.

Col1 Col2 Col3 Expected/Output

--------------------------------------------------

10 20 ? G1

20 30 ? G1

40 50 ? G1

30 50 ? G1

60 70 ? G2

70 80 ? G2

80 90 ? G2

65 75 ? G3

I want Col3 to be populated with an unique value for a single group in Col1 & Col2.

Here Suppose,

10 is matching with 20 20 is matching with 30

40 is matching with 50 30 is matching with 50

Then obviously 10, 20, 30, 40, 50 are in the same group

and has to be identified with same value (anything) for those records in Col3 say G1 (as shown in Expected Output).

Similarly,

60 is matching with 70 70 is matching with 80 80 is matching with 90

Thus 70, 80, 90 are in the same group and

has to be identified with same value (anything) for those records in Col3 say G2.

65 is matching with 75 - So Col3 by G3

and so on..... for huge amount of data.

Without having the concept of Stored Procedure - through Teradata SQL how will I achieve that?

Please help asap...

Thanks,

ANIMESH DUTTA
2 REPLIES
Enthusiast

Re: Recursive Updation in Query

Personally, I would set the group id to the first member of the group!

The script below gives you the result you are looking for though - for the data you gave.

HTH

Create Volatile Table TblA
(Col1 Smallint Not Null
, Col2 Smallint Not Null
, Col3 Smallint
, GroupId Char(3))
Unique Primary Index (Col1)
On Commit Preserve Rows
;

Insert Into Tbla (Col1, Col2) Values (10,20);
Insert Into Tbla (Col1, Col2) Values (20,30);
Insert Into Tbla (Col1, Col2) Values (40,50);
Insert Into Tbla (Col1, Col2) Values (30,50);
Insert Into Tbla (Col1, Col2) Values (60,70);
Insert Into Tbla (Col1, Col2) Values (70,80);
Insert Into Tbla (Col1, Col2) Values (80,90);
Insert Into Tbla (Col1, Col2) Values (65,75);

-- Create a work table to form a natural hierarchy

Create Volatile Table WTblA
(Col1 Smallint Not Null
, Col2 Smallint Not Null
, RealityFlag Char Not Null
)
Primary Index (Col1)
On Commit Preserve Rows
;

Insert Into WTblA
Select Col1, Col2 , 'R'
From TblA
;

-- Any associations not in direct hierarchy,
-- insert them.
-- Always ensure the lower key value is in col1
-- to avoid circular relationships in later recursion!

Insert Into WTblA
Select
CASE WHEN A.Col1 < B.Col1
THEN A.COL1
ELSE B.Col1
END
, CASE WHEN A.Col1 < B.Col1
THEN B.COL1
ELSE A.Col1
END
, 'U' -- Set as unreal so we do not place in final result!
From TblA A
Join TblA B
On A.Col2 = B.Col2
Where Not (
(A.Col1, B.Col1) In
(Select Col1,Col2 From TblA)
Or A.Col1 = B.Col1)
;

-- Set up table with the associations to groups

Create Volatile Table WTblB As (

WITH RECURSIVE FullRelns (GrpId, Col1 , Col2, Level, RealityFlag) AS (
Select Col1, Col1, Col2, 0 as Level, RealityFlag
From WTblA Root
Where Col1 Not In
(Select Col2 From WTblA)
UNION ALL
Select Direct.GrpId
, Indirect.Col1
, Indirect.Col2
, Direct.Level + 1
, Indirect.RealityFlag
From FullRelns Direct
, WTblA Indirect
Where Direct.Col2 = Indirect.Col1
And Direct.Level < 10 ) -- Just to prevent runaway!
Select * From FullRelns
)
WITH DATA
Primary Index (Col1)
ON COMMIT PRESERVE ROWS
;

-- Transform the group id from first member of group to an integer

Select Col1
, Col2
, NewGrp
, _Latin 'G'||Trim(NewGrp) As GroupId
From
( Select
GrpId
, Row_Number() Over (Order By GrpId) As NewGrp
From (Select Distinct GrpId From WTblB) As DI1) As D1
Join WTblB
On WTblB.GrpId = D1.GrpId
Where RealityFlag = 'R'
Order By 1,2
;

/* Final Result Follows

Col1 Col2 NewGrp GroupId
10 20 1 G1
20 30 1 G1
30 50 1 G1
40 50 1 G1
60 70 2 G2
65 75 3 G3
70 80 2 G2
80 90 2 G2

*/

Enthusiast

Re: Recursive Updation in Query

Jimm - It's a very good solution indeed from you, for the current data I've provided.
But suppose the data is as follows - then it's giving no result.
For the below example it should give ONE Group at the end.
The table TBLA - in this case shouldn't have any UPI.

INSERT INTO TBLA (COL1, COL2) VALUES(1, 1);
INSERT INTO TBLA (COL1, COL2) VALUES(1, 2);
INSERT INTO TBLA (COL1, COL2) VALUES(2, 1);
INSERT INTO TBLA (COL1, COL2) VALUES(2, 2);
INSERT INTO TBLA (COL1, COL2) VALUES(2, 6);
INSERT INTO TBLA (COL1, COL2) VALUES(3, 3);
INSERT INTO TBLA (COL1, COL2) VALUES(3, 4);
INSERT INTO TBLA (COL1, COL2) VALUES(3, 7);
INSERT INTO TBLA (COL1, COL2) VALUES(4, 3);
INSERT INTO TBLA (COL1, COL2) VALUES(4, 4);
INSERT INTO TBLA (COL1, COL2) VALUES(4, 6);
INSERT INTO TBLA (COL1, COL2) VALUES(5, 5);
INSERT INTO TBLA (COL1, COL2) VALUES(5, 7);
INSERT INTO TBLA (COL1, COL2) VALUES(6, 2);
INSERT INTO TBLA (COL1, COL2) VALUES(6, 4);
INSERT INTO TBLA (COL1, COL2) VALUES(6, 6);
INSERT INTO TBLA (COL1, COL2) VALUES(7, 3);
INSERT INTO TBLA (COL1, COL2) VALUES(7, 5);
INSERT INTO TBLA (COL1, COL2) VALUES(7, 7);

How'll we achieve for this result !!!