RE: using STRSQL statement fails but works with Ops-Nav

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Charles, 

1)  -- not likely case but...

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzms
tintsel.htm

Do a search on the web site listed above for "recursive table expression"
and see if that might apply to this statement.

2)  -- likely case 

If that's not the case and since you're on V5R2, could there possibly be the
case where one path is hitting SQE and the other CQE and one of the paths
has a bug in support for having correlated sub-query in the projection part
of the statement (piiall - (select ....))?
It wouldn't be the first time where SQE and CQE didn't exactly jive with
each other.

Elvis

At 01:28 PM 12/23/2003 -0500, you wrote:
>The following SQL statement works fine from the Ops-Nav "Run SQl
Statements"
>and as a Query Manager SQL query.  But when run from the STRSQL screen, it
>returns no records yet it doesn't give any error message either.
>
>Note that the QM query and the STRSQL are done interactively from the same
>job, so it isn't a problem with different files being used.
>
>I'm guessing STRSQL has a problem with the common-table expression or the
>correlated sub-query.
>
>Has anyone seen this before?
>
>Thanks,
>Charles
>
>with tbl as (select pimodl, piiall, dochdt
>              from mason/mppriv, mason/tpdocu
>              where pimodl = domelc and dochdt >= 20031223
>              group by pimodl, piiall, dochdt
>              order by pimodl, dochdt asc)
>
>select tbl.pimodl, tbl.piiall, tbl.dochdt, (piiall -
>         (select sum(b.doqty)
>             from mason/tpdocu b
>             where b.domelc = tbl.pimodl
>               and b.dochdt between 20031223 and tbl.dochdt
>           ))  as EndingQty
>from tbl
>order by 1, 3 asc