|
|
|
 |
 |
Archives of the TeradataForumMessage Posted: Fri, 16 Dec 2005 @ 11:14:10 GMT
Hi all, I have a table that has (amongst its 15 fields) an Id, a Year and a Status Code. For each Id, there are multiple years, with different status codes. The table is very large (over 208 million records). In the past, I have used either of the following syntax to collate the data for each Id into a single row:
Option One
==========
SELECT Id
,CASE SUM(
CASE WHEN Yr = 1997
AND Rtn_Sts_Cd = 'A'
THEN 1
WHEN Yr = 1997
AND Rtn_Sts_Cd = 'B'
THEN 2
WHEN Yr = 1997
AND Rtn_Sts_Cd = 'C'
THEN 3
WHEN Yr = 1997
AND Rtn_Sts_Cd = 'D'
THEN 4
(etc)
ELSE 0
END
)
WHEN 1
THEN 'A'
WHEN 2
THEN 'B'
WHEN 3
THEN 'C'
WHEN 4
THEN 'D'
(etc)
END AS Sts_Cd_97
,CASE SUM(
CASE WHEN Yr = 1998
AND Rtn_Sts_Cd = 'A'
THEN 1
WHEN Yr = 1998
AND Rtn_Sts_Cd = 'B'
THEN 2
WHEN Yr = 1998
AND Rtn_Sts_Cd = 'C'
THEN 3
WHEN Yr = 1998
AND Rtn_Sts_Cd = 'D'
THEN 4
(etc)
ELSE 0
END
)
WHEN 1
THEN 'A'
WHEN 2
THEN 'B'
WHEN 3
THEN 'C'
WHEN 4
THEN 'D'
(etc)
END AS Sts_Cd_98
,(etc)
FROM Role_Year
GROUP BY Id
;
OPTION 2
========
SELECT t0.Id
,t1.Rtn_Sts_Cd AS Sts_Cd_97
,t2.Rtn_Sts_Cd AS Sts_Cd_98
,t3.Rtn_Sts_Cd AS Sts_Cd_99
,t4.Rtn_Sts_Cd AS Sts_Cd_00
(etc)
FROM Role_Year AS t0
LEFT OUTER JOIN
Role_Year AS t1
ON t1.Id = t0.Id
LEFT OUTER JOIN
Role_Year AS t2
ON t2.Id = t0.Id
(etc)
WHERE t0.Yr = 1997
AND t1.Yr = 1998
AND t2.Yr = 1999
AND t3.Yr = 2000
AND t4.Yr = 2001
;
As you can see, both methods are quite cumbersome (keeping in mind that I have skipped over large portions of the repetitive code). I should point out that both of these bits of code work quite effectively, they just annoy me because they aren't "pretty". We upgraded to v2r6 on the weekend, and I have been playing around a little with Recursive Queries. I thought I could use this piece of code, setting the Sts_Cd to a default ' ', and then recursively producing a new record with the previous, and next Sts_Cd populated:
WITH RECURSIVE MyYears
( Id
,Cycle_Year
,Sts_Cd_97
,Sts_Cd_98
,Sts_Cd_99
,Sts_Cd_00
,Sts_Cd_01
,Sts_Cd_02
,Sts_Cd_03
,Sts_Cd_04
) AS
(
SELECT Id
,1997
,Rtn_Sts_Cd
,' '
,' '
,' '
,' '
,' '
,' '
,' '
FROM Role_Year
WHERE Yr = 1997
UNION ALL
SELECT my.Id
,my.Cycle_Year + 1
,my.Sts_Cd_97
,CASE WHEN ry.Yr = my.Cycle_Year + 1
THEN Rtn_Sts_Cd
ELSE my.Sts_Cd_98
END
,CASE WHEN ry.Yr = my.Cycle_Year + 1
THEN Rtn_Sts_Cd
ELSE my.Sts_Cd_99
END
,CASE WHEN ry.Yr = my.Cycle_Year + 1
THEN Rtn_Sts_Cd
ELSE my.Sts_Cd_00
END
,CASE WHEN ry.Yr = my.Cycle_Year + 1
THEN Rtn_Sts_Cd
ELSE my.Sts_Cd_01
END
,CASE WHEN ry.Yr = my.Cycle_Year + 1
THEN Rtn_Sts_Cd
ELSE my.Sts_Cd_02
END
,CASE WHEN ry.Yr = my.Cycle_Year + 1
THEN Rtn_Sts_Cd
ELSE my.Sts_Cd_03
END
,CASE WHEN ry.Yr = my.Cycle_Year + 1
THEN Rtn_Sts_Cd
ELSE my.Sts_Cd_04
END
FROM MyYears AS my
INNER JOIN
pinc.Role_Year AS ry
ON ry.Id = my.Id
AND ry.Yr = my.Cycle_Year + 1
AND my.Cycle_Year < 2005
)
SELECT *
FROM MyYears
WHERE Cycle_Year = 2004
ORDER BY TFN ASC
;
The problem I have is that the table size will increase exponentially (and quickly bring my system to a grinding halt) with each recursion that occurs. What I would ideally like to do is limit the FROM clause in the recursive select to:
WHERE my.Cycle_Year
IN (
SELECT MAX(Cycle_Year) FROM MyYears
)
Obviously, I can't do that because I can't use either derived tables or aggregates in the recursive part of the query. I was curious as to whether anyone can suggest an alternative that would reduce the number of rows generated by the recursive query. A recursive update would be great, but I can't see how it would work. Andrew Young
| ||||||||||||||||||||||||||||||||||||||||||||||
| | ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
| Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||
