Thanks for the question, Rajini.
Asked: February 23, 2019 - 5:15 am UTC
Last updated: February 23, 2019 - 6:45 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi All,
I need your inputs and suggestions for the below ;
I have 2 column say A and B . In simple words they want to find percentage .But before that they are performing few validations
In excel they had these validation before deriving the percentage value.
IFERROR(IF(A<0,"N/A",IF(A=0,IF(B>0,"N/A",IF(B=0,0,"N/A")),IF(B=0,IF(A >0,"N/A",IF(A=0,0,"N/A")),IF(((A-B)/ABS(B))>9.999,"N/A",IF(((A-B)/ABS(B))<-9.999,"N/A",(A-B)/ABS(B)))))*-1),"N/A")
I have converted the above condition into corresponding SQL as below
CASE
WHEN A < 0 THEN 'N/A'
WHEN A = 0 OR B = 0 THEN 0
WHEN (A = 0 AND B > 0) OR (B = 0 AND A > 0) THEN 'N/A'
WHEN (A-B)/B < 9.99 OR (A-B)/B > 9.99 THEN 'N/A'
WHEN A IS NULL OR B IS NULL THEN 'N/A'
ELSE round(((A-B)/B),3)
END RESULT
When I execute the above query I was getting error saying data inconsistency..
So I changed all the above to String as Output
Modified the query
CASE
WHEN A < 0 THEN 'N/A'
WHEN A IS NULL OR B IS NULL THEN 'N/A'
WHEN (A = 0 AND B > 0) OR (B = 0 AND A > 0) THEN 'N/A'
WHEN (A-B)/B < 9.99 OR (A-B)/B > 9.99 THEN 'N/A'
WHEN A = 0 OR B = 0 THEN 'N/A'
ELSE CONCAT(( SUBSTR (TO_CHAR((A-B)/ABS(B)),1,3)),'%')
ELSE CONCAT(( SUBSTR (TO_CHAR((A - B)/ABS(C))),1,4)),'%')
END RESULT
When I change the format to String .. I am not able to do any any custom formatting on that String Output..
That is if it is negative show in RED color and within Bracket ..
or just 2 digit in integer and on digit in Decimal place..
Just a mock up output for your reference :-
N/A
0%
0%
0%
0%
N/A
N/A
.048%
N/A
0%
-.00%
N/A
N/A
0%
0
N/A
0%
N/A
N/A
N/A
0%
N/A
0%
0%
They are expecting format like this
(2.5%)
(1.8%)
14.6%
4.8%
15.7%
(1.8%)
So I am not able to do any custom formatting on that. Is there any work around or it cant be done ..
Need your inputs and suggestions.
Thanks ,
Rajini N
and Connor said...
You need to make a decision on which data type (numeric or string) best suits your needs for the application. For example, if its just rendering that is most important, you can go with string, whereas if you need to do arithmetic (eg a grand total) then perhaps numeric is better.
Assuming you're interested in rending strings as output, you need to do *all* of the formatting in the case statement (eg brackets for negative etc). In terms of color of the output, your client code would need to interpret the *string* output to do (eg "is there a leading bracket?").
Personally I think a better way is to just output a *numeric* datatype (because that's what a percentage really is) and just return null for the "n/a" cases. Depending on what client tool you are using, it may be able to auto render a null as "n/a" whilst still keeping the benefits of a having a truly numeric column.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment