Home » Developer & Programmer » Reports & Discoverer » ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01858: a non-numeric character was found where a numeric was expected [message #234923] Thu, 03 May 2007 08:26 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I'm writing a sql plus report and running it through the Oracle Apps for an AP report. The sql below is giving me problems within the apps. Can anyone advise on what i should do? I tried putting in a hard coded date and the report works fine, but I need to pass variables as such below. Please help if you can.


SELECT /*+ INDEX_JOIN(C) */ vendor_id

FROM APPS.PO_VENDORS C

where months_between ('&&as_of_date', trunc(C.creation_date)) >= '&&months_inactive' --to_number('&&months_inactive')


ERROR

Enter value for as_of_date: EXEC FND_CONC_STAT.COLLECT;
Enter value for months_inactive:
where months_between ('EXEC FND_CONC_STAT.COLLECT;', trunc(C.creation_date)) >= '' --to_number('')
*
ERROR at line 7:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: ORA-01858: a non-numeric character was found where a numeric was expected [message #235028 is a reply to message #234923] Thu, 03 May 2007 15:59 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It would fail anywhere, not only in the Apps.

First of all, always use TO_DATE function when dealing with dates (which are entered as strings).

MONTHS_BETWEEN returns a number, so you can't compare it with a date. I saw that you tried to use TO_NUMBER with a (let's say) date value, but that's wrong; you can't just change date into a number. There is a way to do that - Julian date (but, to do that, you'd have to use TO_CHAR, not TO_NUMBER).

Query which doesn't end up with an error would / could look like this:
SELECT vendor_id
FROM po_vendors
WHERE MONTHS_BETWEEN(TO_DATE('&as_of_date', 'dd.mm.yyyy'), TRUNC(creation_date)) >= 10;

As I don't know what should that be >= of, I put a constant (10). If you can explain what you, actually, want, we might be able to help you further.
Previous Topic: Discovere in /opendb format
Next Topic: How do I begin to create a letter
Goto Forum:
  


Current Time: Sat Dec 03 20:29:27 CST 2016

Total time taken to generate the page: 0.06912 seconds