https:

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

 < 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

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2005 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback