Re: SQL - group by not working for me.

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.?