Re: Fw: Embedded SQL error

1) Why are you doing this as dynamic SQL? I see no reason to do so..

Do it as static, it's easier as you don't have to fool with all the quotes.

exec SQL declare C1 cursor for
select
T4.trmifl,
TIMESTAMP(T1.ISIHDATE
, CAST(SUBSTR(DIGITS(T1.IHTIME),1,2)
|| '.' ||
SUBSTR(DIGITS(T1.IHTIME),3,2)
) as time
)

It's also safer as the code can't be attacked by SQL injection.
Dynamic SQL is a no-no in any language from a secure coding / PCI
compliance standpoint. The only justifiable reason for dynamic SQL is
when the table(s) to be queried aren't known till run time. (And
there's often ways around that!)

2) Build some UDF's to move the conversions out of the query
exec SQL declare C1 cursor for
select
T4.trmifl,
toTimestamp(T1.ISIHDATE, T1.IHTIME),


HTH,
Charles


On Wed, Jun 22, 2011 at 8:55 PM, Richard Reeve <richreeve@xxxxxxxxx> wrote:

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
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.