AW: SQL build file of dates

["BirgittaHauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

I'd suggest to generate an UDTF (User Defined Table Function), that can
built an temporary File containing Year and Month and can be called with
parameters and used like any other table or view.

The following example generates an UDTF that returns a table with 24
Year/Month combinations:
CREATE FUNCTION MySchema/DateSeq (
       ParYear  Dec(4, 0),        
       ParMonth Dec(2, 0))
       RETURNS TABLE (RtnYear Dec(4, 0),
                      RtnDate Dec(2, 0))
       LANGUAGE SQL
       DETERMINISTIC
       MODIFIES SQL DATA
       CALLED ON NULL INPUT
       NO EXTERNAL ACTION
       DISALLOW PARALLEL
    BEGIN
       DECLARE Error DECIMAL(1, 0);
       DECLARE Counter   Integer Not Null Default 0;
       DECLARE TempYear  Decimal(4, 0);
       DECLARE TempMonth Decimal(2, 0);
       DECLARE Continue Handler FOR SQLEXCEPTION Set Error = 1;
       DECLARE GLOBAL TEMPORARY TABLE TempTable
              (RtnYear  Dec(4, 0) ,
               RtnMonth Dec(2, 0))
       With Replace;
        
       Set TempMonth   = ParMonth;
       Set TempYear    = ParYear;           
       WHILE Counter < 24
          DO  Set Counter = Counter + 1;
              Insert into Qtemp/TempTable Values(TempYear, TempMonth);
              If   TempMonth >= 12 Then Set TempMonth = 1; 
                                        Set TempYear  = TempYear  + 1;
              Else Set TempMonth = TempMonth + 1;
              End If;         
       END WHILE;          
       RETURN ( SELECT * FROM Qtemp/TempTable); 
    END ;

This UDTF can be called as follows:
select * 
   from Table(DateSeq(Cast(2003 as Dec(4, 0)), Cast(7 as Dec(2, 0)))) as x;

It also can be joined with an other table:
select * 
    from      Table(DateSeq(Cast(2003 as Dec(4, 0)), Cast(7 as Dec(2, 0))))
x
         join MyTable on RtnYear = MyYear;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) 



-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Joe Pluta
Gesendet: Friday, December 29, 2006 18:14
An: 'RPG programming on the AS400 / iSeries'
Betreff: RE: SQL build file of dates


Or you can use that as a CTE, and then go from there, although I'm still a
little fuzzy on the next step; I think you'd then have to use a UNION, with
24 JOINs.

Joe

> From: Vernon Hamberg
>
> If you need the year and month in numeric form for joining, you might 
> try this - I am putting them in character form, too.
>
> SELECT
> digits(decimal(year(current date - 24 month), 4)) || 
> digits(decimal(month(current date - 24 month), 2)) month01, 
> digits(decimal(year(current date - 23 month), 4)) || 
> digits(decimal(month(current date - 23 month), 2)) month02,
(...)