| Query error - ORA-01722: invalid number [message #11071] |
Thu, 04 March 2004 10:39  |
Priyanka Shah
Messages: 19
Registered: January 2003
|
Junior Member |
|
|
Hi I am having a error while doing this query..
This works below:
select wgremdt_id from wgremdt where wgremdt_dept_code = '1' and wgremdt_stat_code = (select wurstat_stat_code from wurstat where wurstat_stat_desc = 'Active') and to_char(wgremdt_equal_date,'MM/DD/YYYY') = to_char('03/04/2004','MM/DD/YYYY') and wgremdt_class_code = '2'
But when I change the number from wgremdt_class_code to be 3 it doesnt work and gives this error?
Also when I remove the portion which says >and wgremdt_class_code = '2'< it gives the same error..
Why does it work for one number and not the other?
|
|
|
|
| Re: Query error - ORA-01722: invalid number [message #11073 is a reply to message #11071] |
Thu, 04 March 2004 11:33  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You have an extra TO_CHAR in there. No need to convert the date string to a string - it already is a string:
to_char(wgremdt_equal_date, 'MM/DD/YYYY') = <b>'03/04/2004'</b>
The better way to write this would be:
wgremdt_equal_date = to_date('03/04/2004', 'mm/dd/yyyy')
You may need a TRUNC function around the column name if you are storing a time component.
|
|
|
|
| Re: Query error - ORA-01722: invalid number [message #11093 is a reply to message #11073] |
Fri, 05 March 2004 04:00 |
Priyanka Shah
Messages: 19
Registered: January 2003
|
Junior Member |
|
|
Thanks Todd that works !
I was aware that sysdate assigns a time but I didnt realize that when again I was calling the column where the date was assigned using sysdate.. I would have to round it because what I was comparing the date to was a date which had a time of 12AM by default. Looks like when I use to_date and it takes default time as 12AM..
This clarifies things immensely !
Really appreciate it.
Priyanka
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
