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

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(?, ?) ";

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


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

This thread ...

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].