Re: SQL - group by not working for me.

[CRPence <CRPbottle@xxxxxxxxx>]

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

Regards, Chuck

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