Solved

ORA-01858

Posted on 2002-03-19
5
1,120 Views
Last Modified: 2010-05-18
I need an urgent solution:

Please look at the following tow queries:

ERROR:
select TB_TRS_LP.MKT_BID_RATE, PUBLISH_DT from TB_TRS_LP where TB_TRS_LP.SWIFT_CCY_CODE='HKD' and TB_TRS_LP.TENOR='O/N' and TO_DATE(TB_TRS_LP.PUBLISH_DT,'dd/MM/YYYY hh:mi:ss AM') <= TO_DATE('', 'dd/MM/YYYY hh:mi:ss AM') AND BKG_CODE='SIN' ORDER BY PUBLISH_DT DESC

OK:
select TB_TRS_LP.MKT_BID_RATE, PUBLISH_DT from TB_TRS_LP where TB_TRS_LP.SWIFT_CCY_CODE='USD' and TB_TRS_LP.TENOR='O/N' and TO_DATE(TB_TRS_LP.PUBLISH_DT,'dd/MM/YYYY hh:mi:ss AM') <= TO_DATE('', 'dd/MM/YYYY hh:mi:ss AM') AND BKG_CODE='HK' ORDER BY PUBLISH_DT DESC


Both the queries I am running through a java program seocnd query is running fine but first query is giving
"ORA-01858: a non-numeric character was found where a numeric was expected"

My problem is that the  errors should come either for both the queries or for none of them as the TO_DATE() function is having the same values in both of the queries.

I have confirmed that the error is becuase of TO_DATE() function other fields in the queries are fine.


thanks in advance
regards
Alok
0
Comment
Question by:asharma2
5 Comments
 
LVL 6

Expert Comment

by:M-Ali
ID: 6879847
Hi,

Is this a typo:
.....
and TO_DATE(TB_TRS_LP.PUBLISH_DT,'dd/MM/YYYY hh:mi:ss AM') <= TO_DATE('', 'dd/MM/YYYY hh:mi:ss AM')

You are trying to compare a date which is less than or equal to a null?? The result of this will be undefined.

Your query which is working fine, is working properly?

Ali
0
 
LVL 5

Expert Comment

by:KarcOrigin
ID: 6879946
Dear Alok,
This may help you!

The Cause is that the input data to be converted using a date format model is incorrect. The input data did not contain a number where a number was required by the format model. Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

Thanks.

0
 
LVL 7

Expert Comment

by:waynezhu
ID: 6879957
You SQL statement is of no problem. The problem is in the data itself. For example, in your first query,
when BKG_CODE='SIN', the PUBLISH_DT contains a non-numeric value.

Compare the examples below:

SQL> select TO_DATE('a') from dual;
select TO_DATE('a') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL> select TO_DATE('') from dual;

TO_DATE('
---------
0
 
LVL 22

Accepted Solution

by:
DrSQL earned 200 total points
ID: 6880073
Alok,
   One more piece to the puzzle.  I think you're operating off an incorrect assumption.  It is not necessarily true that both queries would have the same error.  The error you're getting is an evaluation error - which means that it only happens if that portion of the WHERE clause is evaluated in order to qualify a row.

   I'm going to guess that the second (OK) query returns no rows.  But, since all the conditions are "AND"ed together, the first one that fails disqualifies the row.  The order of evaluation would be index-column-based conditions first, then unmodified columns, then altered columns (the to_date).  It's probably not finding a USD, O/N, HK before it even looks at the to_date.  The other one is finding a HKD, O/N, SIN it just has a bad value in the date and triggers the error.

   I think the best fix to your code is to check for a null instead of that "<=" condition and do some further testing on the contents of PUBLISH_DT.  Once you've cleaned that up or found a better way to exclude the bad enties, you could handle nulls like this:

if you want ONLY where the date is null:

TB_TRS_LP.PUBLISH_DT IS NULL

if you were trying to use the to_date to find null or invalid dates, you'll have to create your own function to do this - to_date will just fail:

create or replace function validate_date(datestr varchar2, fmt varchar2) return date is
cnvdate date;
begin
  cnvdate := to_date(datestr, fmt);
  return(cnvdate);
exception when others then return(null);
end;

select ...
validate_date(TB_TRS_LP.PUBLISH_DT,'dd/MM/YYYY hh:mi:ss AM') is null
...

Good luck!
0
 

Author Comment

by:asharma2
ID: 6890145
Hi All,
         Thanks to you all for contributing towards this problem.I changed the query a bit then it worked fine.I modified it as:
select TB_TRS_LP.MKT_BID_RATE, PUBLISH_DT from TB_TRS_LP where TB_TRS_LP.SWIFT_CCY_CODE='HKD' and TB_TRS_LP.TENOR='O/N'
and TO_CHAR(TB_TRS_LP.PUBLISH_DT,'dd/MM/YYYY hh:mi:ss AM') <= "program_variable for date"  AND BKG_CODE='SIN' ORDER BY PUBLISH_DT DESC;
Probably TO_DATE() was not being used correctly.
But the thing to ponder is,  the same query was working in an other installation .So the same query was working with oracle 8.1.7 and it was failing with 8.1.7.2.Still I am not sure about the real cause of the problem.Is it because of oracle version difference?????

Anyway , It was a good analysis from DrSQL , which prompted me to think this solution.So full marks to DrSQL.
and thanks to everyone who helped in giving the ideas.
regards
Alok

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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

17 Experts available now in Live!

Get 1:1 Help Now