SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to use SUM() in Update Set Case statement to check for condition

user13115886Mar 26 2016 — edited Mar 27 2016

Hi,

I am using sum() function in the case statement to update a column. Its saying aggregate function is not allowed in case as shown below:

Please advise.

UPDATE F_X_Y_DETAILS

SET Y_PRODUCT_TYPE_NEW =

CASE

WHEN SUM (NVL PRICE, 0)) <= 0

THEN

'Misc'

ELSE

CASE

WHEN A = '2S'

AND (SUM (NVL PRICE, 0)) >= 100)

THEN

'Comp'

ELSE

CASE

WHEN A LIKE '%S'

AND (SUM (NVL PRICE, 0)) >= (.85 * (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details)))

THEN

'Serv'

ELSE

CASE

WHEN A IN ('3P', '7P', '8P') AND (SUM(NVL (PRICE,0)) >=(.85* (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details)))

THEN

CASE

WHEN (A = '3P' AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0))FROM f_X_Y_details WHERE A = '7P')))

AND (A = '3P' AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A ='8P')))

THEN

'Phn'

ELSE

CASE

WHEN (A = '8P' AND (SUM(NVL ( PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '7P')))

AND (A = '8P' AND (SUM(NVL ( PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '3P')))

THEN

'Vd'

ELSE

'Misc'

END

ELSE -- go to 3

CASE

WHEN A IN ('1P', '2P') AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '5P'))

AND A IN ('1P', '2P') AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '6P'))

AND A IN ('1P', '2P') AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '9P'))

THEN

'UC'

ELSE

CASE

WHEN A = '5P' AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A IN ('1P','2P')))

AND A = '5P' AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '6P'))

AND A = '5P' AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '9P'))

THEN

'CC'

ELSE

CASE

WHEN A = '6P' AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A IN('1P','2P')))

AND A = '6P' AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '5P'))

AND A = '6P' AND (SUM(NVL (PRICE,0)) > (SELECT SUM(NVL (PRICE,0)) FROM f_X_Y_details WHERE A = '9P'))

THEN

'Networking'

ELSE

'IPO'

END

END;

Thanks

This post has been answered by Paulzip on Mar 27 2016

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 24 2016

Added on Mar 26 2016

5 comments

2,547 views