Wilder1626
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
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,
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.
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.
ASKER
I will look at this SQL*Plus.
Now with the update query to:
I still have:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
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,
I still have:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all, now it works.
It takes a long time to run but it works
It takes a long time to run but it works
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.