Re: SQL build file of dates

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

Bruce

Paul R. begins to suggest a solution - you can generate the whole 24 
dates in a single select statement - that statement could be 
encapsulated in a QM query and run when you do the report - it can be 
directed to an output file with a *REPLACE option. You can do a 
SELECT against the system-provided file SYSIBM/SYSDUMMY1, as he said.

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,
digits(decimal(year(current date - 22 month), 4)) ||
digits(decimal(month(current date - 22 month), 2)) month03,
digits(decimal(year(current date - 21 month), 4)) ||
digits(decimal(month(current date - 21 month), 2)) month04,
digits(decimal(year(current date - 20 month), 4)) ||
digits(decimal(month(current date - 20 month), 2)) month05,
digits(decimal(year(current date - 19 month), 4)) ||
digits(decimal(month(current date - 19 month), 2)) month06,
digits(decimal(year(current date - 18 month), 4)) ||
digits(decimal(month(current date - 18 month), 2)) month07,
digits(decimal(year(current date - 17 month), 4)) ||
digits(decimal(month(current date - 17 month), 2)) month08,
digits(decimal(year(current date - 16 month), 4)) ||
digits(decimal(month(current date - 16 month), 2)) month09,
digits(decimal(year(current date - 15 month), 4)) ||
digits(decimal(month(current date - 15 month), 2)) month10,
digits(decimal(year(current date - 14 month), 4)) ||
digits(decimal(month(current date - 14 month), 2)) month11,
digits(decimal(year(current date - 13 month), 4)) ||
digits(decimal(month(current date - 13 month), 2)) month12,
digits(decimal(year(current date - 12 month), 4)) ||
digits(decimal(month(current date - 12 month), 2)) month13,
digits(decimal(year(current date - 11 month), 4)) ||
digits(decimal(month(current date - 11 month), 2)) month14,
digits(decimal(year(current date - 10 month), 4)) ||
digits(decimal(month(current date - 10 month), 2)) month15,
digits(decimal(year(current date -  9 month), 4)) ||
digits(decimal(month(current date -  9 month), 2)) month16,
digits(decimal(year(current date -  8 month), 4)) ||
digits(decimal(month(current date -  8 month), 2)) month17,
digits(decimal(year(current date -  7 month), 4)) ||
digits(decimal(month(current date -  7 month), 2)) month18,
digits(decimal(year(current date -  6 month), 4)) ||
digits(decimal(month(current date -  6 month), 2)) month19,
digits(decimal(year(current date -  5 month), 4)) ||
digits(decimal(month(current date -  5 month), 2)) month20,
digits(decimal(year(current date -  4 month), 4)) ||
digits(decimal(month(current date -  4 month), 2)) month21,
digits(decimal(year(current date -  3 month), 4)) ||
digits(decimal(month(current date -  3 month), 2)) month22,
digits(decimal(year(current date -  2 month), 4)) ||
digits(decimal(month(current date -  2 month), 2)) month23,
digits(decimal(year(current date -  1 month), 4)) ||
digits(decimal(month(current date -  1 month), 2)) month24
from sysibm/sysdummy1

The result is as follows if run today - change the numbers if you 
have to include the current month.

MONTH01  MONTH02  MONTH03  MONTH04  MONTH05  MONTH06  MONTH07 
MONTH08  MONTH09  MONTH10  MONTH11  MONTH12  MONTH13  MONTH14 
MONTH15  MONTH16  MONTH17  MONTH18  MONTH19  MONTH20  MONTH21 
MONTH22  MONTH23  MONTH24
200412   200501   200502   200503   200504   200505   200506   200507 
  200508   200509   200510   200511   200512   200601   200602 
200603   200604   200605   200606   200607   200608   200609   200610   200611

You can put the SELECT into a QMQRY - use STRQM to get there. Then 
use STRQMQRY in a CL or QCMDEXC to generate the list - be sure to use 
OUTPUT(*OUTFILE) MBROPT(*REPLACE).

HTH
Vern


At 08:09 AM 12/29/2006, you wrote:


> Billy:
>
> <snip>
>  What is the format of the database file you want to select from?
>    Do you have any indexes( i.e. keys ) over it?
> </snip>
>
> That's my problem...I'm not selecting from a file...I can pass a parm or
> substitution value into a QMQRY that contains a start date (or year and
> month), but I'm not selecting "from" anything.  Perhaps that makes it
> impossible to do in SQL.
>
> In a HLL program I can do a loop and increment from a passed-in value, but
> I'm trying to see if there is an SQL way of doing the same.  From what I
> have been able to determine from the manuals and previous experience, a
> "select" statement requires a "from" clause, which requires a file...and I
> don't have an input file.
>
> If this is out of the realm of possibility for SQL...that's fine.  The right
> tool for the right job, and all that.  But since most of the rest of this
> particular process is a series of QMQRYs, I thought it would be good to be
> consistent.
>
> Thanks,
> --Bruce Guetzkow
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.