Re: Dynamically creating a view

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

+1

Substitution markers in the SELECT statement - like &START and &END - 
then SETVAR parameter of STRQMQRY does the trick.

Have an index to support the selectivity, this then gives similar 
simplicity as a VIEW.

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.tX1gOpTw.63anPZuS@centurylink.net> CAAC 
(COMMON Americas Advisory Council) IBM Influencer 2023

On 6/13/2025 2:51 PM, Roger Harman wrote:
> It's kind of old school, but I used to use QMQRY extensively for parameterized queries.
>
> Roger Harman
> COMMON Certified Application Developer - ILE RPG on IBM i on Power
>
>
>
> -----Original Message-----
> From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Bill and Lisa Howie
> Sent: Friday, June 13, 2025 9:40 AM
> 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
> athttps://archive.midrange.com/midrange-l.
>
> Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.