Re: Ask for help.

["Chittari, Raghuram" <1CHITTARR@TOYSRUS.COM>]

        Ngo Baochau ,

        the select statement should have the following GROUP BY
statement :

        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

raghu

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