Re: Ask for help.

[Art DiRamio <ADiRamio@OPENSOLUTIONS.COM>]

Need to group by all fields in the select that are not a function (ie:
everything but SUM(B.BAL_AMT)).

With that, you may want to pick off some of that other stuff in the select
in a separate procedure.

 - - Art  :-)


                -----Original Message-----
                From:   baochaun@mail.transcontinental.ca
[SMTP:baochaun@mail.transcontinental.ca]
                Sent:   Thursday, November 12, 1998 5:01 PM
                To:     'SQR-USERS@USA.NET'
                Subject:        Ask for help.

                Hi everybody

                        I have problem with my select, if every body could
help me, here is my select, here is the error ORA-00937 NOT A SINGLE-GROUP
GROUP FUNCTION.  In this select, I want to have a total of b.bal_amt only on
cust_id.  How can fix it?

                        Thank you for your help.

                select
                B.CUST_ID,
                U.NAME1,
                SUM(B.BAL_AMT),
                B.ITEM,
                B.ITEM_LINE,
                B.CURRENCY_CD,
                B.DUE_DT,
                B.ACCOUNTING_DT,
                B.ASOF_DT,
                CC.CR_LIMIT,
                B.BAL_CURRENCY

                FROM
                  PS_ITEM B,
                  PS_CUSTOMER U,
                  PS_CUST_OPTION CO,
                  PS_CUST_CREDIT CC,
                  PS_SET_CNTRL_REC V,
                  PS_BUS_UNIT_TBL_FS L

                WHERE
                  B.CUST_ID = U.CUST_ID AND
                  B.BUSINESS_UNIT = L.BUSINESS_UNIT AND
                  U.SETID = V.SETID AND
                  V.SETCNTRLVALUE = B.BUSINESS_UNIT AND
                  CO.EFFDT = (SELECT MAX(COA.EFFDT)
                                      FROM PS_CUST_OPTION COA
                                      WHERE CO.SETID = COA.SETID
                                        AND CO.CUST_ID = COA.CUST_ID
                                          AND COA.EFFDT <= sysdate) AND
                  CO.SETID = U.SETID AND
                  CO.CUST_ID = U.CUST_ID AND
                  CO.DST_ID_AR = 'AR_EXT' AND
                  CC.EFFDT =(SELECT MAX(EFFDT) FROM PS_CUST_CREDIT
                                     WHERE CC.SETID = SETID
                                         AND CC.CUST_ID = CUST_ID
                                         AND EFFDT <= sysdate) AND
                  U.SETID = CC.SETID AND
                  U.CUST_ID = CC.CUST_ID AND
                  V.RECNAME = 'CUSTOMER'
                GROUP BY
                 B.CUST_ID

                Merci

                Ngo Baochau
                baochaun@mail.transcontinental.ca
                tel: (514) 335 - 1466/4943
                http://www.transcontinental.ca