As an aside instead of converting the numeric date into a date, I'd convert
the (Current_Date - 7 days) expression into a numeric date in the format
YYYYMMDD.
It does not only make coding easier, it may perform better because an index
created over the POORDT can be used. If you convert the numeric date into a
real date the query optimizer cannot use a (regular Binary Radix Tree)
Index.
This is the where condition I'd use:
Where poordt > Dec(Current_Date - 7 days, 8, 0)
BTW it also includes poordt = 99999999 does not have to be converted.
I assume your example will also crash if POORDT includes 999999999 because
it cannot be converted into a real date!
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"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)
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] Im Auftrag
von smith5646midrange@xxxxxxxxx
Gesendet: Thursday, 02.2 2023 00:05
An: 'Midrange Systems Technical Discussion'
Betreff: RE: How can I get this RUNSQL statement to work?
What if you change the double quotes to two single quotes?
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of a4g
atl
Sent: Wednesday, February 1, 2023 5:51 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: How can I get this RUNSQL statement to work?
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.
--
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.
--
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
