All Forums Database
ANIMESH.DUTTA 35 posts Joined 05/09
03 Sep 2009
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 ? G120 30 ? G140 50 ? G130 50 ? G160 70 ? G270 80 ? G280 90 ? G265 75 ? G3I 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 3040 is matching with 50 30 is matching with 50Then 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 90Thus 70, 80, 90 are in the same group andhas to be identified with same value (anything) for those records in Col3 say G2.65 is matching with 75 - So Col3 by G3and 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

Animesh Dutta Teradata Certified Master
Jimm 298 posts Joined 09/07
06 Sep 2009

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. HTHCreate 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 hierarchyCreate Volatile Table WTblA (Col1 Smallint Not Null, Col2 Smallint Not Null, RealityFlag Char Not Null)Primary Index (Col1)On Commit Preserve Rows; Insert Into WTblASelect 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 WTblASelect 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 AJoin TblA BOn A.Col2 = B.Col2Where 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 DATAPrimary 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*/

ANIMESH.DUTTA 35 posts Joined 05/09
07 Sep 2009

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

Animesh Dutta
Teradata Certified Master

You must sign in to leave a comment.