Re: SQL build file of dates -- RPG400-L

Bruce

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

If you need the year and month in numeric form for joining, you mighttry 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 youhave to include the current month.

MONTH01 MONTH02 MONTH03 MONTH04 MONTH05 MONTH06 MONTH07MONTH08 MONTH09 MONTH10 MONTH11 MONTH12 MONTH13 MONTH14MONTH15 MONTH16 MONTH17 MONTH18 MONTH19 MONTH20 MONTH21MONTH22 MONTH23 MONTH24200412 200501 200502 200503 200504 200505 200506 200507200508 200509 200510 200511 200512 200601 200602200603 200604 200605 200606 200607 200608 200609 200610 200611

You can put the SELECT into a QMQRY - use STRQM to get there. Thenuse STRQMQRY in a CL or QCMDEXC to generate the list - be sure to useOUTPUT(*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.