Re: SQL - group by not working for me.

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

select mnuprf, mnugrp, mnuopt, mnucmd, mnuonm, max(mltimstmp)
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;

Could IFNULL the MAX() aggregate to a zero value for a quick test
without dealing with the FETCH [to add indicator variables].

On 19-May-2011 13:57 , Gqcy wrote:

I have made one more "tweak" and am getting different results I
have changed the join to a left outer join, so those users that
have NEVER ran the job are listed...

However, in RPGLE, I am getting duplicate data instead of correct
data (If they never have ran the option, the previous record's
data is being displayed) example from STRSQL:

MNUPRF MNUGRP MNUOPT MNUCMD
ANORVELL M 4 CALL ORD500C
ASLOUGH O 6 CALL ORD500C
ASULLENS OE 1 CALL ORD500C
BCHURI ZA 23 CALL ORD500C
BHELMEID AO 1 CALL ORD500C
BPATEL ZA 23 CALL ORD500C
CHANCOCK M 8 CALL ORD500C
CLOOS M 8 CALL ORD500C
CNOBLE ZM 8 CALL ORD500C
CVONHOLT O 6 CALL ORD500C
CWEISS ZA 23 CALL ORD500C
CWIBERG M 4 CALL ORD500C
DACHARYA ZA 23 CALL ORD500C


MENUPF ANORVELL M 4.00 CALL ORD500C
MENUPF ASLOUGH O 6.00 CALL ORD500C
MENUPF ASULLENS OE 1.00 CALL ORD500C
MENUPF ASULLENS OE 1.00 CALL ORD500C -
MENUPF BHELMEID AO 1.00 CALL ORD500C
MENUPF BHELMEID AO 1.00 CALL ORD500C -
MENUPF BHELMEID AO 1.00 CALL ORD500C -
MENUPF CLOOS M 8.00 CALL ORD500C
MENUPF CLOOS M 8.00 CALL ORD500C -
MENUPF CVONHOLT O 6.00 CALL ORD500C
MENUPF CVONHOLT O 6.00 CALL ORD500C -
MENUPF CWIBERG M 4.00 CALL ORD500C
MENUPF DACHARYA ZA 23.00 CALL ORD500C