This discussion is archived
7 Replies Latest reply: Sep 28, 2013 9:54 AM by 998158 RSS

Issue with to_char in sql

998158 Newbie
Currently Being Moderated

Hi,

 

I am trying to execute below statment which gives me error:

 

select

CASE

WHEN BAL < =0

THEN 0

ELSE to_char(ABS(BAL),'fm9999999999999999990.90')

END BALANCE

from ACCOUNT_TABLE

 

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

  • 1. Re: Issue with to_char in sql
    SomeoneElse Guru
    Currently Being Moderated

    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.

  • 2. Re: Issue with to_char in sql
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

    998158 wrote:

     

    Hi,

     

    I am trying to execute below statment which gives me error:

     

    select

    CASE

    WHEN BAL < =0

    THEN 0

    ELSE to_char(ABS(BAL),'fm9999999999999999990.90')

    END BALANCE

    from ACCOUNT_TABLE

     

    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:

     

    CASE

        WHEN  bal < 0     -- NOT <=

        THEN  '0'         -- This is a VARCHAR2, to match what the ELSE branch returns

        ELSE  TO_CHAR (bal, 'fm'fm9999999999999999990.90')

    END

    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.

  • 3. Re: Issue with to_char in sql
    Etbin Guru
    Currently Being Moderated

    You don't need to call abs for bal > 0

     

    Regards

     

    Etbin


    Should have looked up before posting

  • 4. Re: Issue with to_char in sql
    Ora-aff Newbie
    Currently Being Moderated

    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


  • 5. Re: Issue with to_char in sql
    Etbin Guru
    Currently Being Moderated

    Even then

     

    select to_char(0005,'fm9999999999999999990.90')

      from dual

     

    TO_CHAR(0005,'FM9999999999999999990.90')
    5.00

     

    Regards

     

    Etbin

  • 6. Re: Issue with to_char in sql
    nickwoodward Newbie
    Currently Being Moderated

    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'

  • 7. Re: Issue with to_char in sql
    998158 Newbie
    Currently Being Moderated

    Hi Etbin,

     

    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.

     

    select

    CASE

    WHEN BAL > =0

    THEN 0

    ELSE to_char(ABS(BAL),'fm9999999999999999990.90')

    END BALANCE

    from ACCOUNT_TABLE

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points