Skip to Main Content

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

question on case statement

manojthakuriiApr 11 2022

Hello Sir,

The below is my case statement which is an expression.

There are 3 meters with me and 3 attribute values

  1. Complete stack expression

  2. c1 expression

  3. c2 expression

--expression for Complete Stack

WHEN (CASE WHEN INLINE_VIEW.ASSET_TYPE='CVT' THEN
CASE
WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER is not null and
PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER is not null
AND PIVOT_ASSETMETER_1.COMPLETESTACKMETER != 0
AND PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER != 0 THEN
CASE
-- Normal
WHEN (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) >= -5
AND (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) < 10 THEN 'Normal'
WHEN (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) >=10
AND (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) < 15
or (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) < -5
AND (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) >= -10 THEN 'Borderline'
WHEN (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) >=15
AND (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) < 20
OR (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) < -10
and (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) >= -15 THEN 'Very Poor'
WHEN (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) >=20
and (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) < 100
OR (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) < -15
and (((PIVOT_ASSETMETER_1.COMPLETESTACKMETER - PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)/PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER)*100) >= -100 THEN 'Not Applicable'
END
ELSE
CASE
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) >= -5
AND (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) < 10 THEN 'Normal'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) >= 10
AND (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) < 15
OR (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) < -5 AND
(((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) >= -10 THEN 'Borderline'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) >= 15
AND (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) < 20
OR (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) < -10
and (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) >= -15 THEN 'Very Poor'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) >=20
and (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) < 100
OR (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) < -15
AND (((CASE WHEN PIVOT_ASSETMETER_1.COMPLETESTACKMETER=0 OR PIVOT_ASSETMETER_1.COMPLETESTACKMETER IS NULL THEN PIVOT_ASSETMETER_1.COMPLETE_STACKPREVMETER ELSE PIVOT_ASSETMETER_1.COMPLETESTACKMETER END - PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000)/(PIVOT_ASSETATTR_1.TOTALCAPACITANCENVALUE * 1000000))*100) >= -100 THEN 'Not Applicable'
END
END
END)

Like wise I have same calculation for C1

(CASE WHEN INLINE_VIEW.ASSET_TYPE='CVT' THEN
CASE
WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER is not null and
PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER is not null
AND PIVOT_ASSETMETER_1.C1CAPACITNCEMETER != 0
AND PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER != 0
THEN
CASE
-- Normal
WHEN (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) >= -5
AND (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) < 5 THEN 'Normal'
WHEN (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) >=5
AND (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) < 10
OR (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) < -5
AND (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) >= -10 THEN 'Borderline'
WHEN (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) >=10
AND (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) < 15
OR (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) < -10 AND
(((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) >= -15 THEN 'Very Poor'
WHEN (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) >= 15 and
(((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) < 100
OR (((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) < -15 and
(((PIVOT_ASSETMETER_1.C1CAPACITNCEMETER - PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)/PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER)*100) >=-100 then 'Not Applicable'
END
ELSE
CASE
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) >= -5
AND (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) < 5 THEN 'Normal'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) >= 5
AND (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) < 10
OR (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) < -5 AND
(((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) >= -10 THEN 'Borderline'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) >=10
AND (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) < 15
OR (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) < -10 AND
(((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) >= -15 THEN 'Very Poor'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) >= 15 and
(((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) < 100
OR (((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) < -15 AND
(((CASE WHEN PIVOT_ASSETMETER_1.C1CAPACITNCEMETER=0 OR PIVOT_ASSETMETER_1.C1CAPACITNCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C1CAPACTIANCEPREVMETER else PIVOT_ASSETMETER_1.C1CAPACITNCEMETER END - PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.TOPUNITCAPACITANCEATT * 1000000))*100) >= -100 THEN 'Not Applicable'
END
END
END)

and like wise i have for c2 meter as well
(CASE WHEN INLINE_VIEW.ASSET_TYPE='CVT' THEN
CASE
WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER is not null and
PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER is not null
AND PIVOT_ASSETMETER_1.C2CAPACITANCEMETER != 0
AND PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER != 0
THEN
CASE
-- Normal
WHEN (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) >= -5
AND (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) < 5 THEN 'Normal'
WHEN (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) >=5
AND (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) < 10
OR (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) < -5
AND (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) >= -10 THEN 'Borderline'
WHEN (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) >=10
AND (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) < 15
OR (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) < -10 AND
(((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) >= -15 THEN 'Very Poor'
WHEN (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) >= 15 and
(((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) < 100
OR (((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) < -15 and
(((PIVOT_ASSETMETER_1.C2CAPACITANCEMETER - PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)/PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER)*100) >=-100 then 'Not Applicable'
END
ELSE
CASE
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) < -5
AND (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) < 5 THEN 'Normal'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) >= 5
AND (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) < 10
OR (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) < -5 AND
(((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) >= -10 THEN 'Borderline'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) >=10
AND (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) < 15
OR (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) < -10 AND
(((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) >= -15 THEN 'Very Poor'
WHEN (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) >= 15 and
(((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) < 100
OR (((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) < -15 AND
(((CASE WHEN PIVOT_ASSETMETER_1.C2CAPACITANCEMETER=0 OR PIVOT_ASSETMETER_1.C2CAPACITANCEMETER IS NULL THEN PIVOT_ASSETMETER_1.C2CAPACITANCEPREVMETER else PIVOT_ASSETMETER_1.C2CAPACITANCEMETER END - PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000)/(PIVOT_ASSETATTR_1.BOTUNITCAPACITANCEATT * 1000000))*100) >= -100 THEN 'Not Applicable'
END
END
END)

the first case expression statement is for complete statck meter and second case statement is for c1 meter

from the above case expression i am calculating as shown below many permutations combination to populate normal values,very poor and borderline values

the code functionality is proper but can we reduce the code as

WHEN EXPRESSION.COMPLETESTACKMETERCALC = 'Normal'
and EXPRESSION.C1CAPACITANCEMETERALC IS NULL
and EXPRESSION.C2CAPACITANCEMETERCALC IS NULL THEN 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
and EXPRESSION.C1CAPACITANCEMETERALC = 'Normal'
and EXPRESSION.C2CAPACITANCEMETERCALC IS NULL then 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
and EXPRESSION.C1CAPACITANCEMETERALC IS NULL
and EXPRESSION.C2CAPACITANCEMETERCALC = 'Normal' then 'Normal' can be change to one statement

CASE WHEN AHI_ASSETS_INSTRUMENT_TF.ASSET_TYPE IN('NCT','CT','VT') THEN 'Not Applicable'

WHEN EXPRESSION.COMPLETESTACKMETERCALC = 'Normal'
and EXPRESSION.C1CAPACITANCEMETERALC IS NULL
and EXPRESSION.C2CAPACITANCEMETERCALC IS NULL THEN 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
and EXPRESSION.C1CAPACITANCEMETERALC = 'Normal'
and EXPRESSION.C2CAPACITANCEMETERCALC IS NULL then 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
and EXPRESSION.C1CAPACITANCEMETERALC IS NULL
and EXPRESSION.C2CAPACITANCEMETERCALC = 'Normal' then 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC = 'Borderline'
and EXPRESSION.C1CAPACITANCEMETERALC IS NULL
and EXPRESSION.C2CAPACITANCEMETERCALC IS NULL then 'Borderline'

when EXPRESSION.COMPLETESTACKMETERCALC IS NULL
and EXPRESSION.C1CAPACITANCEMETERALC = 'Borderline'
and EXPRESSION.C2CAPACITANCEMETERCALC is null then 'Borderline'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
AND EXPRESSION.C1CAPACITANCEMETERALC IS NULL
AND EXPRESSION.C2CAPACITANCEMETERCALC ='Borderline' then 'Borderline'

WHEN EXPRESSION.COMPLETESTACKMETERCALC = 'Very Poor'
and EXPRESSION.C1CAPACITANCEMETERALC IS NULL
and eXPRESSION.C2CAPACITANCEMETERCALC IS NULL then 'Very Poor'

when EXPRESSION.COMPLETESTACKMETERCALC is null
and EXPRESSION.C1CAPACITANCEMETERALC ='Very Poor'
and eXPRESSION.C2CAPACITANCEMETERCALC is null then 'Very Poor'

when EXPRESSION.COMPLETESTACKMETERCALC is null
and EXPRESSION.C1CAPACITANCEMETERALC is null
and eXPRESSION.C2CAPACITANCEMETERCALC ='Very Poor' then 'Very Poor'

when EXPRESSION.COMPLETESTACKMETERCALC = 'Not Applicable'
and EXPRESSION.C1CAPACITANCEMETERALC is null
and EXPRESSION.C2CAPACITANCEMETERCALC is null then 'Not Applicable'

when EXPRESSION.COMPLETESTACKMETERCALC is null
and EXPRESSION.C1CAPACITANCEMETERALC ='Not Applicable'
and EXPRESSION.C2CAPACITANCEMETERCALC is null then 'Not Applicable'

when EXPRESSION.COMPLETESTACKMETERCALC is null
and EXPRESSION.C1CAPACITANCEMETERALC is null
and EXPRESSION.C2CAPACITANCEMETERCALC ='Not Applicable' then 'Not Applicable'

WHEN EXPRESSION.COMPLETESTACKMETERCALC ='Normal'
and EXPRESSION.C1CAPACITANCEMETERALC = 'Normal'
and EXPRESSION.C2CAPACITANCEMETERCALC ='Normal' then 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
AND EXPRESSION.C1CAPACITANCEMETERALC ='Normal'
AND EXPRESSION.C2CAPACITANCEMETERCALC='Normal' THEN 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC ='Normal'
AND EXPRESSION.C1CAPACITANCEMETERALC is null
AND EXPRESSION.C2CAPACITANCEMETERCALC='Normal' THEN 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC ='Normal'
AND EXPRESSION.C1CAPACITANCEMETERALC ='Normal'
AND EXPRESSION.C2CAPACITANCEMETERCALC is null THEN 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC='Normal'
AND EXPRESSION.C1CAPACITANCEMETERALC='Normal'
AND EXPRESSION.C2CAPACITANCEMETERCALC='Borderline' then 'Borderline'

WHEN EXPRESSION.COMPLETESTACKMETERCALC='Borderline'
AND EXPRESSION.C1CAPACITANCEMETERALC='Normal'
AND EXPRESSION.C2CAPACITANCEMETERCALC='Normal' then 'Borderline'

WHEN EXPRESSION.COMPLETESTACKMETERCALC ='Normal'
AND EXPRESSION.C1CAPACITANCEMETERALC='Borderline'
AND EXPRESSION.C2CAPACITANCEMETERCALC='Normal' then 'Borderline'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
AND EXPRESSION.C1CAPACITANCEMETERALC='Normal'
and EXPRESSION.C2CAPACITANCEMETERCALC='Borderline' then 'Borderline'

WHEN EXPRESSION.COMPLETESTACKMETERCALC='Normal'
and EXPRESSION.C1CAPACITANCEMETERALC='Borderline'
and EXPRESSION.C2CAPACITANCEMETERCALC is null then 'Borderline'

WHEN EXPRESSION.COMPLETESTACKMETERCALC='Normal'
and EXPRESSION.C1CAPACITANCEMETERALC is null
and EXPRESSION.C2CAPACITANCEMETERCALC='Borderline' then 'Borderline'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
and EXPRESSION.C1CAPACITANCEMETERALC is null
and EXPRESSION.C2CAPACITANCEMETERCALC='Boarderline' then 'Borderline'

when EXPRESSION.COMPLETESTACKMETERCALC='Normal'
and EXPRESSION.C1CAPACITANCEMETERALC='Normal'
and EXPRESSION.C2CAPACITANCEMETERCALC='Very Poor' then 'Very Poor'

WHEN EXPRESSION.COMPLETESTACKMETERCALC='Normal'
AND EXPRESSION.C1CAPACITANCEMETERALC='Borderline'
AND EXPRESSION.C2CAPACITANCEMETERCALC='Very Poor' THEN 'Very Poor'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
AND EXPRESSION.C1CAPACITANCEMETERALC='Borderline'
AND EXPRESSION.C2CAPACITANCEMETERCALC='Very Poor' THEN 'Very Poor'
--

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
AND EXPRESSION.C1CAPACITANCEMETERALC='Normal'
and EXPRESSION.C2CAPACITANCEMETERCALC='Very Poor' then 'Very Poor'

WHEN EXPRESSION.COMPLETESTACKMETERCALC='Normal'
and EXPRESSION.C1CAPACITANCEMETERALC='Very Poor'
and EXPRESSION.C2CAPACITANCEMETERCALC is null then 'Very Poor'

WHEN EXPRESSION.COMPLETESTACKMETERCALC='Normal'
and EXPRESSION.C1CAPACITANCEMETERALC is null
and EXPRESSION.C2CAPACITANCEMETERCALC='Very Poor' then 'Very Poor'
ELSE NULL

END

Any help would be appreciate to change the belown statement to one statement

WHEN EXPRESSION.COMPLETESTACKMETERCALC = 'Normal'
and EXPRESSION.C1CAPACITANCEMETERALC IS NULL
and EXPRESSION.C2CAPACITANCEMETERCALC IS NULL THEN 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
and EXPRESSION.C1CAPACITANCEMETERALC = 'Normal'
and EXPRESSION.C2CAPACITANCEMETERCALC IS NULL then 'Normal'

WHEN EXPRESSION.COMPLETESTACKMETERCALC IS NULL
and EXPRESSION.C1CAPACITANCEMETERALC IS NULL
and EXPRESSION.C2CAPACITANCEMETERCALC = 'Normal' then 'Normal'

This post has been answered by alvinder on Apr 13 2022
Jump to Answer

Comments

Post Details

Added on Apr 11 2022
33 comments
298 views