|
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 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 copyright@midrange.com.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.