Re: Dynamically creating a view

We use a table named Account_Period_Master in which the accounting periods
with their start and end dates are defined, and a few more settings per
period. Also a flag that indicates if the period is active for reporting.
We join that table in all accounting views, and thus don't have to worry
about recreating things, we only have to set the flag off for the old and
on for the new active period and every accounting query follows that
selection.

SELECT sih.xyz
FROM Sales_Invoice_Header AS sih
JOIN Accounting_Period_Master AS apm
ON (sih.Invoice_Date BETWEEN apm.Start_Date AND apm.End_Date)
AND apm.Active_Reporting_Period_Flag = 1

Best regards,
-Arco


Op vr 13 jun. 2025 18:40 schreef Bill and Lisa Howie <blhowie66@xxxxxxxxxxx

:

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