| ORA-01858: a non-numeric character was found where a numeric was expected [message #234923] |
Thu, 03 May 2007 08:26  |
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  |
 |
Littlefoot
Messages: 21821
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
