ORA-01848 [message #430326] |
Tue, 10 November 2009 01:52  |
b_chugh
Messages: 68 Registered: August 2005 Location: delhi
|
Member |
|
|
Hi All
I am getting this error while executing one of the select which is having the following condition which is failing.
f.PostDate = To_Date(SubStr(fr.RecordData,38,6),'YYYDDD')
RecordData column is varchar type and its contains the flat file's data. So it is having the potential that at this place it is not having the Date information. I need that the select should not give the error and rather if the date is not found at this place then simple it return false and should not fetch the data of the record.
I have tried decode etc but of no help.
Please let me knos if there is a way.
Thanks
|
|
|
|
Re: ORA-01848 [message #430362 is a reply to message #430333] |
Tue, 10 November 2009 03:31   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or you could dof.PostDate = CASE WHEN SubStr(fr.RecordData,38,6) between 1 and 365 THEN To_Date(SubStr(fr.RecordData,38,6),'YYYDDD') else null end
You'll need to include a check to see if the year is a leap year (in which case you need to check date between 1 and 366).
|
|
|
|
|
Re: ORA-01848 [message #430375 is a reply to message #430373] |
Tue, 10 November 2009 04:14   |
b_chugh
Messages: 68 Registered: August 2005 Location: delhi
|
Member |
|
|
But the YYY OR DDD part both have the potential that they have the different data than the valid YYY or DDD number. It may be like '0,1345'.
I have implemented the seperate function to check the date and it seems to be working.
Please let me know if any other solution can be implemented otherwise I will use the function.
Thanks
|
|
|
Re: ORA-01848 [message #430383 is a reply to message #430375] |
Tue, 10 November 2009 04:47  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
It sounds to me like your actual problem is that you are looking for dates in places where there is not date data.
What happens if there is a 6 digit string of numbers at that position where the 4th digit is 0,1 or 2, but the data isn't meant to be a date?
Both my method and the function will validate that you can convert it into a date, but that's no guarantee that you actually want to do so.
On a tangentially related matter, it would be better from a performance point of view if you do it with CASE etc in SQL, as that will avoid excessive context switching when using the new function.
|
|
|