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.
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'