Re: Dynamically creating a view

[Justin Taylor <jtaylor.0ab@xxxxxxxxx>]

Can you create a UDTF instead and make your inputs the parameters?



On Fri, Jun 13, 2025 at 11:40 AM Bill and Lisa Howie <blhowie66@xxxxxxxxxxx>
wrote:

> 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