Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5604
  • Last Modified:

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

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

0
Wilder1626
Asked:
Wilder1626
3 Solutions
 
ajexpertCommented:
Could you please let us know datatype of UNLOAD_MILE_RATE in TL_RATE_DETAIL table?  I suspect it is already VARCHAR2
0
 
DavidSenior Oracle Database AdministratorCommented:
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.
0
 
Wilder1626Author Commented:
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DavidSenior Oracle Database AdministratorCommented:
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.
0
 
Wilder1626Author Commented:
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
0
 
Steve WalesSenior Database AdministratorCommented:
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

0
 
DavidSenior Oracle Database AdministratorCommented:
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.
0
 
Wilder1626Author Commented:
Thanks to all, now it works.

It takes a long time to run but it works
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now