Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01848 (oracle 10g)
ORA-01848 [message #430326] Tue, 10 November 2009 01:52 Go to next message
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 #430333 is a reply to message #430326] Tue, 10 November 2009 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01848: day of year must be between 1 and 365 (366 for leap year)

Create a function that check the date and return a default or null if it is not a valid one.

Regards
Michel
Re: ORA-01848 [message #430362 is a reply to message #430333] Tue, 10 November 2009 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could do
f.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 #430365 is a reply to message #430362] Tue, 10 November 2009 03:35 Go to previous messageGo to next message
b_chugh
Messages: 68
Registered: August 2005
Location: delhi
Member
These 6 characters can be '009345' or '010354' as it is YYYDDD.
Re: ORA-01848 [message #430373 is a reply to message #430365] Tue, 10 November 2009 03:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
My mistake - the CASE check should be checking the DDD part of the string.
Re: ORA-01848 [message #430375 is a reply to message #430373] Tue, 10 November 2009 04:14 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: concatenate result strings
Next Topic: How to return dynamic # of columns from stored procedure?
Goto Forum:
  


Current Time: Tue Sep 27 19:35:16 CDT 2016

Total time taken to generate the page: 0.10865 seconds