When I was doing these date type manipulations in SQL, I found that using a UDF to just bring back a single value into my statement eliminated a lot of potential for errors in the sub stringing, concatenation, etc. Much easier to troubleshoot too. In this case, I'd wrap that UDF in another to handle the necessary quoting.
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 Mark Waterbury
Sent: Wednesday, February 1, 2023 3:20 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: How can I get this RUNSQL statement to work?
Darryl,
For these types of situations, I suggest to declare a variable such as:
DCL &SQL *CHAR 3000
Then use CHGVAR to concatenate the desired values into that &SQL variable ...
That way, you can either "echo" that variable to the job log via SNDPGMMSG, or use "debug" to display the value in that variable, to verify that it looks "correct" (e.g. just like what worked in ACS Run SQL Scripts).
Then, just code the RUNSQL statement like this:
RUNSQL SQL(&SQL) COMMIT(*NC) DATFMT(*ISO) DATSEP(-) OPTION(*LIST)
Hope that helps,
Mark S. Waterbury
On Wednesday, February 1, 2023 at 05:43:24 PM EST, a4g atl <a4ginatl2@xxxxxxxxx> wrote:
RUNSQL SQL('insert into qtemp/PO180M1 (Select * from DPPDATA/PO180M1 wher
e date(substr(digits(poordt),1,4) || "-" || substr(digits(poordt),5,2) ||
"-" || substr(digits(poordt),7,2) ) > (current_date - 7 days) and poordt
<> 999999)') COMMIT(*NC) DATFMT(*ISO) DATSEP(-) OPTION(*LIST)
Column or global variable - not found.
Why does it not like the dash?
I tested the statement in SQL in ACS where it worked.
TIA, Darryl Freinkel.
midrange.com
