Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Oracle - ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

Posted on 2013-01-06

3 solutions

Medium Priority

5,142 Views

Last Modified: 2013-01-06

Hello all

I have an error in the query bellow saying:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

How can i fix this?

The value i have in that column is a rate. I want to have the format 0.00

Thanks again for your help

SELECT 
    'H',
    'M',
    ROW_NUMBER() OVER (ORDER BY TLR.CARRIER_ID) as ROW_NUM,
    TLR.CARRIER_ID,
    LRN.FROM_KEY_NUM,
    LRN.FROM_LOW_KEY_VALUE,
    CASE LRN.FROM_HIGH_KEY_VALUE
        WHEN '0' THEN ''
        ELSE LRN.FROM_HIGH_KEY_VALUE
      END,    
    LRN.FROM_COUNTRY,
    LRN.TO_KEY_NUM,
    LRN.TO_LOW_KEY_VALUE,
     CASE LRN.TO_HIGH_KEY_VALUE
        WHEN '0' THEN ''
        ELSE LRN.TO_HIGH_KEY_VALUE
      END,
    LRN.TO_COUNTRY,
       CASE TRD.UNLOAD_MILE_RATE
        WHEN '0' THEN ''
        ELSE TO_CHAR(TRD.UNLOAD_MILE_RATE,'fm99999.00')
      END,
    TRD.MAX_STOPS
FROM 
    TL_RATE TLR, TL_RATE_DETAIL TRD, LANE_RATE_NETWORK LRN

Open in new window

Comment

Question by:Wilder1626

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

8 Comments

LVL 14

Assisted Solution

by:ajexpert

ajexpert earned 560 total points

ID: 387485162013-01-06

Could you please let us know datatype of UNLOAD_MILE_RATE in TL_RATE_DETAIL table? I suspect it is already VARCHAR2

LVL 23

Expert Comment

by:David

ID: 387485262013-01-06

Try instead, ELSE TO_NUMBER(TRD.UNLOAD_MILE_RATE,'fm99999.00')...

FYI, if you use SQL*Plus to test the statement, the parser will indicate where the error was found -- a faster way to debug the code.

LVL 11

Author Comment

by:Wilder1626

ID: 387485432013-01-06

Hi ajexpert

The UNLOAD_MILE_RATE in TL_RATE_DETAIL is NUMBER(10,2) data type.

Hi dvz,

is SQL*PLUS a module i would have to instal?

I'm pretty new in Oracle sql.

also, i still have:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

with:

 CASE TRD.UNLOAD_MILE_RATE
        WHEN '0' THEN ''
        ELSE TO_NUMBER(TRD.UNLOAD_MILE_RATE,'fm99999.00')
      END,

Open in new window

LVL 23

Expert Comment

by:David

ID: 387487202013-01-06

Ok, I was wrong about changing the function, go back to TO_CHAR. Change the THEN '' to THEN NULL, and try again.

SQL*Plus is a client-side terminal session. With the target username, password, target database (SID), and TCP/IP port, one can get into command line mode without any GUI interference. It's a separate discussion, plenty of how-to in the knowledge base and on Oracle.

LVL 11

Author Comment

by:Wilder1626

ID: 387487352013-01-06

I will look at this SQL*Plus.

Now with the update query to:

    CASE TRD.UNLOAD_MILE_RATE
        WHEN '0' THEN NULL
        ELSE TO_CHAR(TRD.UNLOAD_MILE_RATE,'fm99999.00')
      END,

Open in new window

I still have:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

LVL 23

Accepted Solution

by:Steve Wales

Steve Wales earned 800 total points

ID: 387488162013-01-06

If TRD.UNLOAD_MILE_RATE is a number I think the WHEN is what's causing the problem

You're comparing UNLOAD_MILE_RATE (a number) to '0' (a character).

Try this ?

    CASE TRD.UNLOAD_MILE_RATE
        WHEN 0 THEN NULL
        ELSE TO_CHAR(TRD.UNLOAD_MILE_RATE,'fm99999.00')
      END,

Open in new window

LVL 23

Assisted Solution

by:David

David earned 640 total points

ID: 387488402013-01-06

Good catch; '0' should be 0. However, it raises the question that inbound NULL values should be trapped as well. Something like, when a rate is > 0, then to_char... else NULL.

LVL 11

Author Closing Comment

by:Wilder1626

ID: 387494232013-01-06

Thanks to all, now it works.

It takes a long time to run but it works

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The strange case of the insufficient privileges error when creating a materialized view using execute immediate inside a PL/SQL procedure.

Article by: Javier

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…

Oracle Database

SQL Server - Converting RFC822 Dates Into Any Timezone

Article by: dsacker

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at: ht…