Re: SQL - group by not working for me.

[Gqcy <gmufasa01@xxxxxxxxx>]

THAT WORKS!!!
adding the IFNULL made it work:


exec sql declare LOAD cursor for
 select   mnuprf, mnugrp, mnuopt, mnucmd, mnuonm,
    IFNULL(max(mltimstmp), '0001-01-01-00.00.00.000000')
    from pdatalib/menupf LEFT OUTER JOIN pdatalib/menulog
      on mnuprf = mlnuprf and mnugrp = mlnugrp and mnuopt = mlnuopt
    where mnucmd like (:fmtScanVals)
    Group by mnuprf, mnugrp, mnuopt, mnucmd, mnuonm
    order by mnuprf, mnugrp, mnuopt
    for fetch only;

Thanks so much.

On 5/19/2011 10:29 PM, CRPence wrote:
> I just ran a quick test to mimic the given queries, purposefully having
> failed to add an indicator variable [or coalescing the NULL value to a
> non-NULL value] which effected sqlcode=-305 sqlstate=22002; per SQL0305,
> "Indicator variable required. Cause: A FETCH ... statement has resulted
> in a null value, but an indicator variable was not specified for host
> variable..." and having re-created the condition of "the previous
> record's data [is] being displayed" when presenting the result of the
> host variables after having ignored the error. HTH.
>
> Regards, Chuck
>
> On 19-May-2011 18:43 , CRPence wrote:
> > Is it possible that some FETCH requests are failing in the SQLRPGLE,
> > but those failures are being suppressed\ignored; i.e. the data from
> > the FETCH of the prior row remains in the [host] variables being used
> > for reporting? From what I can tell, seems every row that will need
> > to process a database NULL value is a row which exhibits the problem
> > that is described. Seems possible NULLs would be an issue since the
> > previously coded INNER JOIN would not have had any need to handle
> > the NULL value like is required for the LEFT OUTER JOIN.?