Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 16 Dec 2005 @ 11:14:10 GMT


     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 


Subj:   Question around recursive queries
 
From:   Young, Andrew

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
Queensland, Australia



     
  <Prev Next>  
<<First
<Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023