Re: Ask for help. - Solution

["Campbell, Andrew" <acampbell@UNITEDMEDIA.COM>]

Actually SUM is an aggregate function so that it can not be combined with
individual items..

-----Original Message-----
From: Krishnan Sarangarajan [mailto:ksaranga@RUAN.COM]
Sent: Thursday, November 12, 1998 5:41 PM
To: Multiple recipients of list SQR-USERS
Subject: Re: Ask for help. - Solution


Hi! I believe, if my previous knowledge is correct, if you are using a
"group by" function, all the columns that you have, prior to the single
set function, will need to be defined as part of the select statement.

Therefore, your select statement would look like

> 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
> SUM(B.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
>
> 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
> 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
>
Hope it helps is clarifying why you are getting the error.

Krishnan Sarangarajan
ksaranga@ruan.com
  Ruan Transportation
                3200 Ruan Center (DSM 6th Floor)
 Des Moines, IA  50309
[O] (515) 245-5655
[F] (515) 247-2065

> ----------
> From:
> baochaun@mail.transcontinental.ca[SMTP:baochaun@mail.transcontinental.
> ca] on behalf of Ngo Bao Chau[SMTP:baochaun@MAIL.TRANSCONTINENTAL.CA]
> Sent:         Thursday, November 12, 1998 4: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
>