Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    15

    Unanswered: Getting ORA-00932 inconsistent data type error

    Hello,
    I am getting this error on a varchar2(30) data column that I am trying to convert to number. I am creating a new view against some tables that house converted legacy data. The table is defined with a line_type column and a line_value columns. The line_type will contain either 'AMT', 'DATE' or 'STR'. The line_value column is defined as varchar2(30). Based on the line_type, that will determine how I format the line_value data. I have created the select for my view to translate this using a case statement:

    CASE line_type
    when 'AMT' then round(to_number(line_7_value),2) when 'DATE' then to_date(line_7_value, 'YYYY-MM-DD')
    ELSE
    line_7_value
    END

    I can't figure out what I am doing incorrectly. It does not like the "When 'AMT'" line, gives me an error of 'inconsistent data type, expected NUMBER, got CHAR". The data in line_7_value is defined as varchar2(30), I attempting to use To_number to convert it to a number, then round the number. I don't know why it says it is 'expecting number'. Any help on this would be appreciated!! I have spent the last 4 hours on this with no resolution. THANKS!!!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    try this:
    Code:
    CASE line_type
      WHEN 'AMT'  THEN TO_CHAR(Round(To_number(line_7_value), 2))
      WHEN 'DATE' THEN TO_DATE(line_7_value, 'YYYY-MM-DD')
      ELSE line_7_value
    END
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    That won't work, you are trying to return a character value in one case and a DATE value in other cases for the same column. But a column can only have one single data type across all rows. It can't be a DATE in the first row and a character value in the second row.

    The common "base type" for everything is character, so that's what you need to use:

    Code:
    CASE line_type
      WHEN 'AMT'  THEN TO_CHAR(Round(To_number(line_7_value), 2))
      WHEN 'DATE' THEN to_char(TO_DATE(line_7_value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
      ELSE to_char(line_7_value)
    END

  4. #4
    Join Date
    Aug 2010
    Posts
    15
    This worked!!! THANK YOU, THANK YOU, THANK YOU. I have definetly learned something!!

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Quote Originally Posted by shammat View Post
    That won't work, you are trying to return a character value in one case and a DATE value in other cases for the same column. But a column can only have one single data type across all rows. It can't be a DATE in the first row and a character value in the second row.

    The common "base type" for everything is character, so that's what you need to use:

    Code:
    CASE line_type
      WHEN 'AMT'  THEN TO_CHAR(Round(To_number(line_7_value), 2))
      WHEN 'DATE' THEN to_char(TO_DATE(line_7_value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
      ELSE to_char(line_7_value)
    END
    Actually all you need is:
    PHP Code:
    CASE line_type
      WHEN 
    'AMT'  THEN TO_CHAR(ROUND(TO_NUMBER(line_7_value), 2))
      
    WHEN 'DATE' THEN line_7_value
      
    ELSE line_7_value
    END 
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If that's the case, then WHEN 'DATE' is, actually ELSE, so it is
    Code:
    CASE line_type 
      WHEN 'AMT'  THEN TO_CHAR(ROUND(TO_NUMBER(line_7_value), 2)) 
      ELSE line_7_value 
    END

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Right on!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •