Re: WITH SQL statement via EGL

Jason, I have not used WITH statements within EGL, but I did run into a
problem on V5R4 i5/OS with recursive statements like WITH in another Java
app. In V6R1 i5/OS, this restriction does not exist.

Basically, there are 2 query engines on i5/OS: CQE and SQE. Only SQE
supports recursive WITH statements (and some other more complex queries).
If your JDBC DataSource sort property is not using a Sort Sequence of
sort=hex ( i.e. sort=language, or sort=job, etc), your query on V5R4 will
get dropped down into CQE which won't support the WITH statement.

I couldn't find anything about EGL not supporting WITH, and I don't have
any evidence that it does. But I wanted to mention this gotcha with WITH
on i5/OS in case that's what you encountered. I could test it out if
anyone needs more info - I have a WITH statement I could plug into EGL for
a test run.


******************************************************************************************************

EGL SQL does not support everything that SQL can do.
For instance, it doesn't support the WITH clause
(WITH table1 as ..., table2 as ..., etc).
So the #sql in EGL may not support the timestamp function.

I believe the best solution would be to use a prepared statement.
That's all I use any more because of the control I have over the SQL
statement.

If you have any questions, feel free to email me directly.

Try this:

prepare myStmt from
"select cmtclaim, cmtdate, cmttime, cmtcomm, cmtuser, cmtprior, " +
"cmttype from NTFILES.CLMNOTESL2 where cmtclaim = ? and " +
"timestamp(cmtdate, cmttime) <= timestamp(?, ?) ";

try
get comment with myStmt using claimn, datein, timein;
onException (exc AnyException)
//process exception
end

--Jason

Steve Mervosh
THOR (Total Hardware and Order Repository)
Software Development
IBM Rochester Minnesota
spm@xxxxxxxxxx