'WITH' statement in RPGLE embedded SQL

This might get you close ....

WITH tomorrowTemp as (
  select current date + 1 day as tomorrow
  from   sysibm.sysdummy1
)
select tomorrow
from   tomorrowTemp
 
HTH,
DaveSlash

Select allOpen in new window

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

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

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.  

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                                     

Select allOpen in new window

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?

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                                           
                                                                    
                                                                    
                                                                    

Select allOpen in new window

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                                            
                                                                      

Select allOpen in new window

Thanks for your help!!  If I need to post the 'interactive' question seperately please let me know.