7 Replies Latest reply: Sep 28, 2013 11:54 AM by 998158 RSS

    Issue with to_char in sql

    998158

      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

          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

            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

              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

                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

                  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
                    nick woodward

                    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

                      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