Re: SQL - group by not working for me.

[CRPence <CRPbottle@xxxxxxxxx>]

  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