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
Solved

ORA-01858

Posted on 2002-03-19
5
1,135 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle 11g 23 106
Oracle sql query 7 73
Oracle - SQL Parse String 5 44
any step by steps guide on how to install Oracle 12c on Windows 10 8 56
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

808 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