Create a permanent View and include SQL Global Variables (for the from and
to period).
CREATE VARIABLE YourSchema/GblFromPeriod Timestamp Default
Current_Timestamp;
CREATE VIEW ...
AS SELECT ....
FROM ....
WHERE YourDate between GblFromPeriod and GblToPeriod
Exec SQL Declare Cursor ....
Exec SQL Set GblFromPeriod = Current_Timestamp - 7 Days;
Exec SQL Set GblToPeriod = Current_Timestamp;
Exec SQL Open Cursor;
... Loop through the Cursor like always
The SQL Global Variables are set just before you access the View (with a
Cursor or SELECT ... into or SELECT in ACS) with the SQL SET-Statement.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"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)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Bill
and Lisa Howie
Sent: Friday, 13 June 2025 18:40
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Dynamically creating a view
Hello all,
I have a situation where we have a view that I set up using the SQL script
editor in ACS that we want to incorporate into a program and give it dynamic
parameters. This is a report that we run monthly, so we have a "from
period" and a "to period" that need to change each month when it's run.
I've tried embedding it in an RPG program, complete with the DROP and CREATE
statements for the view. I keep running up against a wall trying to add in
the parameters for the periods, though. The SQL pre-compiler doesn't seem
to like it when I try to use the fields with the colon before them, as in
:FROMPERIOD and :TOPERIOD, and it also doesn't like it when I don't have the
colons in there. I've tried defining the periods in a database file and
also as work fields within the program. When I don't have the colons, the
program runs, but gives an error that it doesn't find the period fields.
At this point I'm falling back to regroup and wondering if I'm setting this
up the right way. Would it be better/more elegant to set up an SQL script
in a source file and use RUNSQLSTM to do it? Or is there a third, better
option that I'm not thinking of? All input would be greatly appreciated.
Thanks!
Bill
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
