RE: Embedded SQL error

Richard,

Don't define your SQL statement in a D spec which doesn't give you enough room to format the code.

It's pretty easy to pick out the problem with the third CAST not being closed correctly once you've reformatted the SQL:

 SELECT T4.trmifl,
TIMESTAMP( T1.ISIHDATE,
 CAST( SUBSTR( DIGITS( T1.IHTIME ), 1, 2 ) || '.' ||
SUBSTR( DIGITS( T1.IHTIME ), 3, 2 ) AS TIME ) ),
T1.ISIHDATE,
CAST( SUBSTR( DIGITS( T1.IHTIME ), 1, 2 ) || '.' ||
 SUBSTR( DIGITS( T1.IHTIME ), 3, 2 ) AS TIME ),   
  T1.IHTIME,
T3.IOSEQ,
T2.IDACODE,
T2.IDAVALU,
T1.IHNOTE,
T4.TRMIRS,
T2.IDACOMM,
  T1.IHRECB,
T4.TRRNAME,
T4.TRMIDT,
T4.TRMITM,
  TIMESTAMP( T1.ISIHDATE,
CAST( SUBSTR( DIGITS( T1.IHTIME ), 1, 2 ) || '.' ||
SUBSTR( DIGITS( T1.IHTIME ), 3, 2 ) AS TIME ) ),
TIMESTAMP( T4.ISTRTRDT,
CAST( SUBSTR( DIGITS( T4.TRTRTM ), 1, 2 ) || '.' ||
SUBSTR( DIGITS( T4.TRTRTM ), 3, 2 ) || '.' ||      -- This isn't closed!     
 TIMESTAMP( T2.IDASYSDT,
CAST( SUBSTR( DIGITS( T2.IDASYSTM ), 1, 2 ) ) || '.' ||
SUBSTR( DIGITS( T2.IDASYSTM ), 3, 2 ) ) || '.' ||       
  SUBSTR( DIGITS( T2.IDASYSTM ), 5, 2 ) AS TIME) ),
  T2.IDASYSDT,
CAST( SUBSTR( DIGITS( T2.IDASYSTM ), 1, 2 ) || '.' ||
SUBSTR( DIGITS( T2.IDASYSTM ), 3, 2 ) || '.' ||
SUBSTR( DIGITS( T2.IDASYSTM ), 5, 2 ) AS TIME ),
TIMESTAMP( T4.TRMIDT, T4.TRMITM )       
FROM SQAFC312.NCPIOHP T1    
INNER JOIN NCPIODPA T2
ON T1.IHPAT = T2.IDAPAT AND
         T1.ISIHDATE = T2.IDADATE AND      
         T1.IHTIME = T2.IDATIME
INNER JOIN NCIOMSP T3
ON T2.IDACODE = T3.IOCODE              
INNER JOIN SQAFC312.NCTRNSP T4
ON T1.IHPAT = T4.TRPAT#  AND     
           T1.ISIHDATE = T4.ISTRACDT AND
           T1.IHTIME = T4.TRACTM AND     
           T4.TRCODE = 'I'             

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Reeve
Sent: Wednesday, June 22, 2011 8:55 PM
To: RPG programming on the AS400 / iSeries
Subject: Fw: Embedded SQL error

All,

 
     I'm going crazy with this SQL stmt - when run I get a 'Token ) was not valid. Valid tokens: + -" (after digits) message in the joblog.  For the life of me, I don't see the problem.  Is there an easier or more intuitive way of debugging embedded SQL?
  'SELECT T4.trmifl, TIMESTAMP(T1.ISIHDATE,  '+ 
 'CAST(SUBSTR(DIGITS(T1.IHTIME),1,2)|| ''.'''+ 
 '|| SUBSTR(DIGITS(T1.IHTIME),3,2) AS TIME) '+ 
 ' ), T1.ISIHDATE,  CAST( SUBSTR(DIGITS     '+ 
 '(T1.IHTIME),1,2) || ''.'' ||              '+ 
 'SUBSTR(DIGITS(T1.IHTIME),3,2) AS TIME ),  '+ 
 'T1.IHTIME, T3.IOSEQ,  T2.IDACODE,         '+ 
 'T2.IDAVALU, T1.IHNOTE, T4.TRMIRS,         '+ 
 ' T2.IDACOMM,  T1.IHRECB, T4.TRRNAME,      '+ 
 ' T4.TRMIDT, T4.TRMITM,                    '+ 
 '  TIMESTAMP(T1.ISIHDATE, CAST(            '+ 
 '  SUBSTR(DIGITS(T1.IHTIME),1,2) ||        '+ 
 '  ''.'' || SUBSTR(DIGITS(T1.IHTIME)       '+ 
 ' ,3,2) AS TIME ) ),                       '+ 
 'TIMESTAMP(T4.ISTRTRDT , CAST(SUBSTR(      '+ 
 'DIGITS(T4.TRTRTM),1,2) ||''.''|| SUBSTR(  '+ 
 'DIGITS(T4.TRTRTM),3,2) ||''.''||          '+ 
 'TIMESTAMP(T2.IDASYSDT , CAST( SUBSTR(     '+ 
 'DIGITS(T2.IDASYSTM),1,2))||''.''|| SUBSTR('+ 
 'DIGITS(T2.IDASYSTM),3,2))||''.''||        '+
 'SUBSTR(DIGITS(T2.IDASYSTM),5,2) AS TIME)),'+
 ' T2.IDASYSDT, CAST(SUBSTR(DIGITS          '+
 '(T2.IDASYSTM),1,2)                        '+
 '||''.''|| SUBSTR(DIGITS(T2.IDASYSTM),3,2) '+
 '||''.''|| SUBSTR(DIGITS(T2.IDASYSTM),5,2) '+
 'AS TIME ) ,                               '+
 '  TIMESTAMP(T4.TRMIDT, T4.TRMITM )        '+
 '  FROM SQAFC312.NCPIOHP T1 INNER JOIN     '+
 '   NCPIODPA T2 ON
T1.IHPAT = T2.IDAPAT AND'+
 '       T1.ISIHDATE = T2.IDADATE AND       '+
 '        T1.IHTIME = T2.IDATIME            '+
 '    INNER JOIN NCIOMSP T3 On              '+
 '     T2.IDACODE = T3.IOCODE               '+
 '         INNER JOIN SQAFC312.NCTRNSP T4 ON'+
 '           T1.IHPAT = T4.TRPAT#  and      '+
 '           T1.ISIHDATE = T4.ISTRACDT AND 
'+
 '           T1.IHTIME = T4.TRACTM AND      '+
 '           T4.TRCODE = ''I''              '
 
As always, thanks for your advice and help.
 
Warmest Regards,

Richard Reeve