Solved

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

Posted on 2013-01-06
8
4,236 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

0
Comment
Question by:Wilder1626
8 Comments
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 140 total points
ID: 38748516
Could you please let us know datatype of UNLOAD_MILE_RATE in TL_RATE_DETAIL table?  I suspect it is already VARCHAR2
0
 
LVL 23

Expert Comment

by:David
ID: 38748526
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 38748543
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
 
LVL 23

Expert Comment

by:David
ID: 38748720
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Author Comment

by:Wilder1626
ID: 38748735
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
 
LVL 22

Accepted Solution

by:
Steve Wales earned 200 total points
ID: 38748816
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
 
LVL 23

Assisted Solution

by:David
David earned 160 total points
ID: 38748840
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
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38749423
Thanks to all, now it works.

It takes a long time to run but it works
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sQL pivot 9 39
Outer Query not returning data - SQL HELP 16 40
Help with SQL Query 23 39
Maintaining Oracle Managed Accounts 2 16
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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now