× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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,
(...)




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.