Home » SQL & PL/SQL » SQL & PL/SQL » Please help me!
Please help me! [message #582709] Mon, 22 April 2013 03:24 Go to next message
mist598
Messages: 905
Registered: February 2013
Location: Hyderabad
Senior Member
Hello everybody!

select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');


Error:-ORA-01858: a non-numeric character was found where a numeric was expected

Re: Please help me! [message #582710 is a reply to message #582709] Mon, 22 April 2013 03:29 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
The error message means what it means

http://www.oracle.com/pls/db112/to_URL?remark=ranked&urlname=http:%2F%2Fdocs.oracle.com%2Fcd%2FE11882_01%2Fserver.112%2Fe17766%2F e1500.htm%23sthref1082
Quote:

ORA-01858: a non-numeric character was found where a numeric was expected
Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.
Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.


Obviously there is a type mismatch and possibly a bad design. Give us your DDL (particularly for ordered_date).

Regards,
Dariyoosh

[Updated on: Mon, 22 April 2013 03:32]

Report message to a moderator

Re: Please help me! [message #582711 is a reply to message #582710] Mon, 22 April 2013 03:38 Go to previous messageGo to next message
mist598
Messages: 905
Registered: February 2013
Location: Hyderabad
Senior Member
Please elaborate , i am not getting..

Thanks,
Re: Please help me! [message #582712 is a reply to message #582711] Mon, 22 April 2013 03:40 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Gives us your CREATE TABLE statement indicating your column types and the sample data (INSERT statements) in the table that can be used as a test case generating the error you got.

Regards,
Dariyoosh

[Updated on: Mon, 22 April 2013 03:40]

Report message to a moderator

Re: Please help me! [message #582714 is a reply to message #582712] Mon, 22 April 2013 03:45 Go to previous messageGo to next message
mist598
Messages: 905
Registered: February 2013
Location: Hyderabad
Senior Member
But i have 177 columns and it is a buit-ins table in Oracle Apps..
Re: Please help me! [message #582716 is a reply to message #582714] Mon, 22 April 2013 03:47 Go to previous messageGo to next message
Roachcoach
Messages: 1200
Registered: May 2010
Location: UK
Senior Member
That where clause looks all kinds of dodgy.
Re: Please help me! [message #582717 is a reply to message #582714] Mon, 22 April 2013 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58960
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
substr(ORDERED_DATE,1,10),'


If ORDERED_DATE is of DATE data type this is silly you cannot SUBSTR on a date you can only do it on a string.

regards
Michel
Re: Please help me! [message #582718 is a reply to message #582717] Mon, 22 April 2013 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58960
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition,

Quote:
ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');


What this is intended to mean?

Regards
Michel
Re: Please help me! [message #582719 is a reply to message #582717] Mon, 22 April 2013 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you tell us what the where clause is supposed to do, then we can tell you what you need to write.
Your current code makes no sense so we can't work out what it should be doing.
Re: Please help me! [message #582726 is a reply to message #582714] Mon, 22 April 2013 05:27 Go to previous messageGo to next message
John Watson
Messages: 4524
Registered: January 2010
Location: Global Village
Senior Member
Quote:
But i have 177 columns and it is a buit-ins table in Oracle Apps..

The column ont.oe_order_headers_all.order_date is a DATE type column.
Re: Please help me! [message #582729 is a reply to message #582726] Mon, 22 April 2013 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58960
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks John, so he has his answer, we just have to wait for his feedback... ./fa/1601/0/

Regards
Michel
Re: Please help me! [message #582732 is a reply to message #582726] Mon, 22 April 2013 05:48 Go to previous messageGo to next message
mist598
Messages: 905
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks,
That is a Date type column and i modified The Query Like This

select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
where ordered_date=:P_ORDERED_DATE;


I got an error
-------------
REP-0613: Value does not match mask 'DD-MON-RR'.
ORA-01861: literal does not match format string

REP-0091: Invalid value for parameter 'P_ORDERED_DATE'.
Re: Please help me! [message #582734 is a reply to message #582732] Mon, 22 April 2013 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58960
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either the parameter should be of DATE datatype, either it is a string and you must use TO_DATE with a format mask that matches your data (string).

Regards
Michel
Re: Please help me! [message #582735 is a reply to message #582734] Mon, 22 April 2013 06:09 Go to previous message
mist598
Messages: 905
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks,

Now i got it..
Previous Topic: Deducing rows
Next Topic: how to list all tables that are connected to each other with constraints and list them all together
Goto Forum:
  


Current Time: Wed Sep 03 03:13:36 CDT 2014

Total time taken to generate the page: 0.20064 seconds