Re: dynamic sql query and variable substituion

[CRPence <CRPbottle@xxxxxxxxx>]

  FWiW, the following two SQL statements are functionally identical:

     "SELECT INTCOL FROM QSYS2/QSQPTABL"

     "SELECT INTCOL FROM QSYS2     /QSQPTABL  "

  That is, the %TrimR() is optional, functionally.

  Also FWiW...

  The claim that the given snippet "works perfect" seems specious. 
 I believe the given snippet was syntactically incorrect due to an 
improperly formed string; I am not even confident all of the clauses 
in the statement are even supported.  Perhaps it was functional as 
given, but if not...

  IMO it is worthwhile, as a courtesy to others who might try to 
copy\paste the given as an example, to clarify if some corrections 
were required to make the snippet functional; there is also the 
archive to consider.  For example, perhaps better to suggest that 
"Aside from the corrections forming the string that was assigned to 
MyStatement"... only then suggesting additionally that the given 
snippet worked perfectly.  Even better, post the version of a 
snippet which was corrected and was found to be functional as 
claimed; i.e. a snippet which legitimately "works".

Regards, Chuck

Ashish Kulkarni wrote:
> Thanks, this works perfect
>
> On Fri, Apr 16, 2010 at 2:17 AM, Birgitta Hauser wrote:
>
> > SELECT ... INTO is not allowed with dynamic SQL,
> > but VALUES INTO is!
> >
> > MyStatement = 
> >   'Values(select date(JOTSTP), time(JOTSTP) +
> >           From %TrimR(templib) + '/' + %TrimR(tempfile) +
> >           ORDER BY JOTSTP  desc +
> >           fetch first 1 row only) into ?, ?';
> >
> > Exec SQL Prepare DynSQL from :MyStatement;
> > Exec SQL Execute DynSQL Using :Date1, :Time1;
> >
> >
> > Ashish Kulkarni wrote:
> >
> > > Where i can pass templib and tempfile from other program, all
> > > the tables will have these 2 columns
> > >
> > > dtemplib          s             10a   inz('QTEMP')
> > > dtempfile         s             10a   inz('myfile')
> > > d date1           s               d
> > > d time1           s               t
> > >
> > > MyStatement = 'select   (date(JOTSTP)),  (time(JOTSTP)) '
> > >   +  'into  :date1 , :time1 from '
> > >   + %TrimR(templib) + '/' + %TrimR(tempfile)
> > >   + ' ORDER BY JOTSTP  desc fetch first 1 row only  ';
> > >
> > > Exec SQL
> > >   Execute Immediate :MyStatement;
> > >
> > > This is not working for me at all