Re: dynamic sql query and variable substituion

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