| Toad related query [message #352471] |
Tue, 07 October 2008 22:00  |
sg52
Messages: 7
Registered: October 2008
|
Junior Member |
|
|
Hi,
I am using oracle 10g release 1.
i am using toad for development process.
I encountered a problem while using substitution variable.
Please look at the code:
SELECT DISTINCT T1.ACCT
,T1.CLS_DATE
,T1.STATUS
FROM ACCOUNT T1, MESSAGE E9
WHERE T1.ACCT = E9.ACCT
AND T1.OPN_D >= TO_DATE('&1') - 60
AND T1.CLS_DATE BETWEEN TRUNC(TO_DATE('&1'),'D') - 1 AND TO_DATE('&1')
AND E9.E99_MSG_C IN ('ABS,'ASD','SDR')
This code runs perfect in SQL*Plus
but when run in TOAD it gives an error:
ORA-01858 a non-numeric character was found where a numeric was expected
I think its due to use of &1 in to_Date()
but why it has problem in TOAD when it works in SQL*Plus.
Is their a specific way to do this in TOAD while using this??
Also, specifying format with to_Date() does not help solving the problem.
Kindly help me on this.
Sandy
|
|
|
|
|
|
|
|
|
|
| Re: Toad related query [message #353594 is a reply to message #352633] |
Tue, 14 October 2008 05:10  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Looks like the default date format of your TOAD differs from the one in sqlplus.
Use an explicit format mask. TO_DATE should be used with two parameters.
Oh, and next time please copy-paste your query IN your post. Saves me the trouble of downloading and opening your attachment.
[Updated on: Tue, 14 October 2008 05:11] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
