Exactly like the error says: inconsitent datatypes: expected NUMBER got CHAR
In a case statement each branch must return the same type. You have two different types, a number and a char string.
I am trying to execute below statment which gives me error:
WHEN BAL < =0
Error: inconsitent datatypes: expected NUMBER got CHAR
It works fine when I do not use to_char function. But gives value as '0' instead of '0.00'.
To get value 0.00, I am trying to_char with fm format which is giving above error. Can anyone let me know if I'm giving something wrong here.
Thanks in advance
If it works fine when you don't use TO_CHAR, then why use TO_CHAR?
What are you trying to do? Post a little sample data (CREATE TABLE and INSERT statements for different numbers) and the exact results you want from that data.
Above, you're saying "bal <= 0", menaing "do the same thing if bal is less than 0, or if bal is exaclty 0". If you want it to do one thing when bal is less than 0, and something else when bal is exactly 0, then use "<' instead of "<=". For example:
WHEN bal < 0 -- NOT <=
THEN '0' -- This is a VARCHAR2, to match what the ELSE branch returns
ELSE TO_CHAR (bal, 'fm'fm9999999999999999990.90')
The 1st part of the CASE expression takes care of all negative numbers, which is the only situation in which bal <> ABS (bal). If control gets as far as the ELSE clause, then bal is the same as ABS (bal), so there's no need to call ABS.
You don't need to call abs for bal > 0
Should have looked up before posting
I think he is using the ABS FOR NUMBERS > 0 for such type of scenarios:
ABS(0005) = '5' and the to_char function to render the output as : 5.00
I'm in no way an expert, and that's an understatement, but think you have to make sure that the output from each of the WHEN/THEN cases is the same datatype.
i'd try this first with single quotes to denote a character literal:
when bal <= 0 then '0'
then it matches the ELSE format.
*edit: or remove the 'to_char'
The reason I used ABS is I have similar logic for -ve values. In this scenario, I should get abs values rather than -ve columns.
WHEN BAL > =0