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

Eric,

I'm on v5r2.

As I said in the original post, I'm not getting any error messages and the
exact same query in QM or Ops-Nav runs fine.

Charles


> -----Original Message-----
> From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx]
> Sent: Tuesday, December 23, 2003 2:36 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: using STRSQL statement fails but works with Ops-Nav
> 
> 
> Charles,
> 
> What version of OS400 are you using.  
> 
> We had trouble recently with a similar statement in embedded 
> SQL where the
> query optimizer returned an invalid floating point value 
> (probably a divide
> by zero, as indicated by second level message) when 
> estimating the cost of a
> join.  The only indication of trouble was a MCH message that 
> reported the
> invalid floating point value.  This was V4R4, so no support 
> available...  
> 
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-898-7863 or ext. 1863
> 
> 
> 
> -----Original Message-----
> From: CWilt@xxxxxxxxxxxx [mailto:CWilt@xxxxxxxxxxxx]
> Sent: Tuesday, December 23, 2003 12:28 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: using STRSQL statement fails but works with Ops-Nav
> 
> 
> 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 
> _______________________________________________
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
> 
> _______________________________________________
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>