Lynn Harris
asked on
'WITH' statement in RPGLE embedded SQL
I would like to use the 'WITH' statement to define multiple dates to simplify my embedded SQL statement. Is this possible? If so, does anyone have an example they could share?
Thanks!
Thanks!
Hi Lynn,
The WITH statement simply declares (defines, describes, etc.) a temporary and/or derived table that will be used in a subquery.
WITH mytable (startdate, enddate)
AS
(
VALUES ('01/01/1980', '12/31/1980)
),
SELECT *
FROM basetable
FULL JOIN mytable
WHERE basetable.posting_date between startdate and enddate;
Kent
The WITH statement simply declares (defines, describes, etc.) a temporary and/or derived table that will be used in a subquery.
WITH mytable (startdate, enddate)
AS
(
VALUES ('01/01/1980', '12/31/1980)
),
SELECT *
FROM basetable
FULL JOIN mytable
WHERE basetable.posting_date between startdate and enddate;
Kent
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To be honest, though, that seems like more work than it's worth ...
Hi Dave,
I actually promote using queries like I showed above. We've got some rather complex queries that filter by date in several locations and I've found that my users are reaonsably capable of changing the dates in one place, but struggle to always "find them all" when they have to change them in 6. The volume of data is relatively small so that even though the full join is less efficient than hard-coding the dates in all of the correct places, it actually saves cycles when one figures in the time lost to rerunning the queries. :)
Kent
I actually promote using queries like I showed above. We've got some rather complex queries that filter by date in several locations and I've found that my users are reaonsably capable of changing the dates in one place, but struggle to always "find them all" when they have to change them in 6. The volume of data is relatively small so that even though the full join is less efficient than hard-coding the dates in all of the correct places, it actually saves cycles when one figures in the time lost to rerunning the queries. :)
Kent
ASKER
Thank you both...These solution are helpful. However, my situation is a little different. I may have asked wrong. I have attached a code example. I want to use the 'WITH' in embedded code with the PREPARE statement.
I have not responded to a post before...... hopefully this is the correct way.
Thanks again.
I have not responded to a post before...... hopefully this is the correct way.
Thanks again.
Code: The code below in failing with SQL0104- & after checking I think one solution would be to use the (cast(&)) and concatenate the year, month, and day. However, this seem extremely length and Im concerned about run time. The database is large. So, after looking more I found the WITH and believe it would be better&. ???
SQLSEL = 'Select DDYEAR, '+
'DDMOTH, '+
'DDDAY, '+
'DDGARO, '+
'DDEYR, '+
'DDEMO, '+
'DDEDA, '+
'DDGARI, '+
'DDMD, '+
'DDCREW '+
'From CDSDDM '+
'Where DDDRV# = ? AND DDPAYI <> '+
QUOTE + 'P' + QUOTE + ' AND '+
'(((? > (DDYEAR || DDMOTH || DDDAY) OR '+
'(? = (DDYEAR || DDMOTH || DDDAY) AND ? >= DDGARO)) AND '+
'((? < (DDEYR || DDEMO || DDEDA) OR '+
'(? = (DDEYR || DDEMO || DDEDA) AND ? <= DDGARI))) OR '+
'((? > (DDYEAR || DDMOTH || DDDAY) OR '+
'(? = (DDYEAR || DDMOTH || DDDAY) AND ? >= DDGARO)) AND '+
'(? < (DDEYR || DDEMO || DDEDA) OR '+
'(? = (DDEYR || DDEMO || DDEDA) AND ? <= DDGARI))) OR '+
'((? < (DDYEAR || DDMOTH || DDDAY) OR '+
'(? = (DDYEAR || DDMOTH || DDDAY) AND ? <= DDGARO)) AND '+
'(? > (DDEYR || DDEMO || DDEDA) OR '+
'(? = (DDEYR || DDEMO || DDEDA) AND ? >= DDGARI))))'+
'Order by DDYEAR, DDMOTH, DDDAY, DDGARO '+
'For Read Only';
*
C PREPLineRun BEGSR
*
C/Exec SQL
C+ PREPARE Stmt1 from :SQLSEL
C/End-Exec
*
/FREE
IF SQLCODE <> 0;
CLEAR ERRMSG;
ERRMSG = 'PREPARE Line Run CURSOR';
EXFMT ERRDSP;
PRMRTN = '2';
EXSR ExitPgm;
ENDIF;
ENDSR;
/END-FREE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent!!
This is my first attempt to use the 'with'. I tried it first with interactive SQL and an have problems. Any suggestions?
General question: Once I accept a solution does that keep additional comments from being made?
This is my first attempt to use the 'with'. I tried it first with interactive SQL and an have problems. Any suggestions?
General question: Once I accept a solution does that keep additional comments from being made?
Token ( was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCE
WITH MYTABLE AS (SELECT DDCREW, DDDRV#,
DATE(DDYEAR || DDMOTH || DDDAY) As strdate, ddmd from cdsddm)
select * from tsttable
where dddrv#=2523
and strdate = 20090316
ASKER
SORRY! I was changing the table name and copied a bad one.
Token ( was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCE
WITH MYTABLE AS (SELECT DDCREW, DDDRV#,
DATE(DDYEAR || DDMOTH || DDDAY) As strdate, ddmd from cdsddm)
select * from MYTABLE
where dddrv#=2523
and strdate = 20090316
ASKER
Thanks for your help!! If I need to post the 'interactive' question seperately please let me know.
This might get you close ....
Open in new window