Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01861: literal does not match format string
ORA-01861: literal does not match format string [message #304039] Mon, 03 March 2008 18:47 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi All,

The below query is working fine.

 SELECT TO_DATE(TO_CHAR(decode(h.mas_close_dt, 0, NULL, h.mas_close_dt)) ||
                       SUBSTR(LPAD(TO_CHAR(decode(h.mas_close_tm, 0, NULL, h.mas_close_tm)), 8, '0'),
                              1,
                              6),
                       'YYYYMMDD HH24MISS') AUTH_CLOSE_DT
          FROM tab1     h,
               tab2            b,
               tab3  sb,
               tab4         s,
               tab5  pt,
               tab6 gs
         WHERE h.mas_basket_id = b.mas_basket_id
           AND sb.subacct_id = h.mas_portfolio_id
           AND h.mas_auth_acct_id = 0
           AND trim(h.mas_cusip) = s.cusip
           AND h.mas_status IN ('P_PASS', 'TRADER', 'NEW')
           AND (h.mas_close_dt IS NULL OR h.mas_close_dt = 0)
           AND b.mas_test_only = 'N'
           AND gs.grp_id = h.mas_portfolio_id
           AND gs.role_cd = 'MGR'
           AND gs.srce_cd = 'ONLINE'
           AND gs.person_id = pt.person_id
           AND h.mas_close_dt = 0
           AND h.mas_qty > 0 
           AND SELECT TO_DATE(TO_CHAR(decode(h.mas_close_dt, 0, NULL, h.mas_close_dt)) ||
                       SUBSTR(LPAD(TO_CHAR(decode(h.mas_close_tm, 0, NULL, h.mas_close_tm)), 8, '0'),
                              1,
                              6),
                       'YYYYMMDD HH24MISS') IS NULL


but the below query returning ORA-01861 error(mentioned in the title)

 
SELECT *
  FROM (SELECT TO_DATE(TO_CHAR(decode(h.mas_close_dt, 0, NULL, h.mas_close_dt)) ||
                       SUBSTR(LPAD(TO_CHAR(decode(h.mas_close_tm, 0, NULL, h.mas_close_tm)), 8, '0'),
                              1,
                              6),
                       'YYYYMMDD HH24MISS') AUTH_CLOSE_DT
          FROM tab1     h,
               tab2            b,
               tab3  sb,
               tab4         s,
               tab5  pt,
               tab6 gs
         WHERE h.mas_basket_id = b.mas_basket_id
           AND sb.subacct_id = h.mas_portfolio_id
           AND h.mas_auth_acct_id = 0
           AND trim(h.mas_cusip) = s.cusip
           AND h.mas_status IN ('P_PASS', 'TRADER', 'NEW')
           AND (h.mas_close_dt IS NULL OR h.mas_close_dt = 0)
           AND b.mas_test_only = 'N'
           AND gs.grp_id = h.mas_portfolio_id
           AND gs.role_cd = 'MGR'
           AND gs.srce_cd = 'ONLINE'
           AND gs.person_id = pt.person_id
           AND h.mas_close_dt = 0
           AND h.mas_qty > 0)
 WHERE AUTH_CLOSE_DT IS NULL

--ORA-01861: literal does not match format string


Here mas_close_dt and mas_close_dt are number fields and having 0 for this where conditions.
Eventually when I hardcode for these fields I dont see any problems.

SELECT *
  FROM (SELECT TO_DATE(TO_CHAR(decode(0, 0, NULL, 0)) ||
                       SUBSTR(LPAD(TO_CHAR(decode(0, 0, NULL, 0)), 8, '0'),
                              1,
                              6),
                       'YYYYMMDD HH24MISS') AUTH_CLOSE_DT
          FROM tab1     h,
               tab2            b,
               tab3  sb,
               tab4         s,
               tab5  pt,
               tab6 gs
         WHERE h.mas_basket_id = b.mas_basket_id
           AND sb.subacct_id = h.mas_portfolio_id
           AND h.mas_auth_acct_id = 0
           AND trim(h.mas_cusip) = s.cusip
           AND h.mas_status IN ('P_PASS', 'TRADER', 'NEW')
           AND (h.mas_close_dt IS NULL OR h.mas_close_dt = 0)
           AND b.mas_test_only = 'N'
           AND gs.grp_id = h.mas_portfolio_id
           AND gs.role_cd = 'MGR'
           AND gs.srce_cd = 'ONLINE'
           AND gs.person_id = pt.person_id
           AND h.mas_close_dt = 0
           AND h.mas_qty > 0)
 WHERE AUTH_CLOSE_DT IS NULL
 


I'm not sure what causing the problem. I was trying to look for any junk character in the column. But I could not.
Any input would be appreciated!!

//Thangam

[Updated on: Mon, 03 March 2008 18:52]

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #304049 is a reply to message #304039] Mon, 03 March 2008 21:43 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Since,you are using the to_date and to_char functions, make sure that the literal that you provide matches the format string that you've specified.

regards,

Re: ORA-01861: literal does not match format string [message #304096 is a reply to message #304039] Tue, 04 March 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So they are some h.mas_close_dt numbers that are not in the format you think they are.

Regards
Michel
Re: ORA-01861: literal does not match format string [message #304198 is a reply to message #304096] Tue, 04 March 2008 08:48 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
This problem has finally solved. Actually in the table tab1, we had record with mas_close_tm = 90000000 and mas_status = 'CLOSED'. We were thinking that since this 90000000 is fall out of timestamp limit, it might have caused the problem. when we eventually update this to proper value(ex: 23452345), it is solved.

Note : mas_close_tm NUMBER(15), mas_status CHAR(8);


But still my buzz is how this records caused the problem even though we are filtering out(using mas_status in ('P_PASS', 'TRADER', 'NEW')) this records in where clause? and I don't see this record when I run the inner query alone. Why is this existing records in the table troubled us though it was filtered in where condition.

Any guess???

//Thangam

[Updated on: Tue, 04 March 2008 08:50]

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #304270 is a reply to message #304198] Tue, 04 March 2008 12:58 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
gold_oracl wrote on Tue, 04 March 2008 09:48

But still my buzz is how this records caused the problem even though we are filtering out(using mas_status in ('P_PASS', 'TRADER', 'NEW')) this records in where clause?


An excellent question that has baffled many before and will baffle many in the future. It doesn't matter if you are filtering out rows with the bad data. For one thing, a full table scan will come across that row.
I remember seeing a great example on Tom Kyte's site, but just do not have the link saved.
Re: ORA-01861: literal does not match format string [message #304297 is a reply to message #304270] Tue, 04 March 2008 15:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
The optimizer may choose an execution plan that tries to check the date format before it filters the rows based on other criteria.
Re: ORA-01861: literal does not match format string [message #304299 is a reply to message #304297] Tue, 04 March 2008 16:21 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
But, Optimizer has to choose the plan based on the filter condition, right?
Re: ORA-01861: literal does not match format string [message #304301 is a reply to message #304299] Tue, 04 March 2008 16:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
It may apply the formatting and conditions in any order it sees fit.
Re: ORA-01861: literal does not match format string [message #304305 is a reply to message #304299] Tue, 04 March 2008 16:44 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Eventually, how to avoid this kind of confusions??
Re: ORA-01861: literal does not match format string [message #304306 is a reply to message #304305] Tue, 04 March 2008 17:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
The best method is to design your data structure so that no bad data can be entered, storing dates and times as dates or timestamps instead of numbers or at least applying appropriate constraints that would prevent bad data.

Alternatively, you could create a user-defined function that would accept the mas_close_dt and mas_close_tm as input parameters, apply your formatting, validate it and either return a valid date or null.

I have seen some people put the filter conditions that would restrict the rows in an inline view and add rownum > 0 or a materialize hint to try to force the optimizer to filter the rows first, but that does not always work.
Re: ORA-01861: literal does not match format string [message #304627 is a reply to message #304306] Wed, 05 March 2008 22:30 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
by the way, I found same kind of discussion from other forum... now I am clear what was going on in CBO... Just go thru these links.....

http://www.gennick.com/madness.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:11504677087008

http://forums.oracle.com/forums/thread.jspa?threadID=618799&start=15&tstart=0

//Thangam
Previous Topic: Adding a Partition to an existing partioned table
Next Topic: Question on ROWNUM.
Goto Forum:
  


Current Time: Fri Dec 02 14:12:35 CST 2016

Total time taken to generate the page: 0.18838 seconds