Solved

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

Posted on 2013-01-06

3 solutions

4,736 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 140 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 22

Accepted Solution

by:Steve Wales

Steve Wales earned 200 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 160 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

Suggested Solutions

Title	# Comments	Views	Activity
error in oracle form	11	53	2017-04-20
MSSQL Query for Selecting the SUM of a Specific Group	2	37	2017-05-08
SQL - Aging Report - Display Months with no data	8	43	2017-05-10
ORACLE SQL DEVELOPER + Compare Tables	5	37	5d

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

Some Useful Settings in JasperReports Server

Article by: Doug

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.

Using the Original Oracle Export and Import Utilities

Video by: Steve

This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.