Re: Ask for help.

[Tony DeLia <tdelia@EROLS.COM>]

Ngo,
   Everyone has been correct in their technical assessment of your
problem regarding GROUP BY / AGGREGATE Functions... Now... From a
functional or "logical" standpoint grouping by all the columns in your
select list will yield nothing more than it would if you placed
b.bal_amt without the SUM function... The ITEM table keys (as you know)
are BUSINESS_UNIT, CUST_ID, ITEM and ITEM_LINE... Eliminate the ITEM
columns completely to obtain the Balance Amount by Customer... OR... Do
you wish to see the ITEM Detail along with the total BAL_AMT on each
line? Depending on the volume in ITEM you can, in addition to the tables
you're joining, Join the item table to itself... Group by all the
columns in your original select list (Maybe add B.BAL_AMT as well)...
And take the sum of the newly joined ITEM table (reflecting the total
for the customer)... See the X table/columns...

It will look something like this...

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

                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,
                  ps_item x

                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'
           and x.business_unit = b.business_unit
           and x.cust_id       = b.cust_id
       GROUP BY B.CUST_ID,
                U.NAME1,
                B.ITEM,
                B.ITEM_LINE,
                B.CURRENCY_CD,
                B.DUE_DT,
                B.ACCOUNTING_DT,
                B.ASOF_DT,
                CC.CR_LIMIT,
                B.BAL_CURRENCY,
                b.bal_amt

I'm assuming ALL original joins in your select are functioning
properly... Also... This could take considerable processing time...

Good Luck with Accounts Receivable!!!  - Tony DeLia



Art DiRamio wrote:
>
> 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

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com