Hello Sir,
The below is my case statement which is an expression.
There are 3 meters with me and 3 attribute values
-
Complete stack expression
-
c1 expression
-
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'