ora-01861 literal does not match format string

Question

select contract.contract_id
from swcd.scd_contract contract 

left join SCD.COMPANY_TIMELINE_VIEW customer on (customer.company_id=contract.customer_company_id and to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '1-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '1-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '1-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '1-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') >= (CASE WHEN customer.EVENT_START_DATE is null THEN to_date('01-JAN-1901', 'DD-Mon-YYYY') else 
trunc(customer.EVENT_START_DATE) END) AND to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '1-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '1-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '1-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '1-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') <= (CASE WHEN customer.EVENT_END_DATE is null THEN to_date('01-JAN-2101', 'DD-Mon-YYYY') else 
trunc(customer.EVENT_END_DATE) END)) 

left join SCD.COMPANY_TIMELINE_VIEW vendor on (vendor.company_id=contract.vendor_company_id and to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '1-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '1-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '1-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '1-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') >= (CASE WHEN vendor.EVENT_START_DATE is null THEN to_date('01-JAN-1901', 'DD-Mon-YYYY') else
trunc(vendor.EVENT_START_DATE) END) AND to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '1-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '1-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '1-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '1-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') <= (CASE WHEN vendor.EVENT_END_DATE is null THEN to_date('01-JAN-2101', 'DD-Mon-YYYY') else 
trunc(vendor.EVENT_END_DATE) END));

This is my query I am executing on Oracle 10g. This shows a strange behavior to me. When I execute the select query with either of join it works fine, But with both the required joins it says ora-01861 literal does not match format string.

-- Works fine
select contract.contract_id
from swcd.scd_contract contract 

left join SCD.COMPANY_TIMELINE_VIEW customer on (customer.company_id=contract.customer_company_id and to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '1-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '1-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '1-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '1-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') >= (CASE WHEN customer.EVENT_START_DATE is null THEN to_date('01-JAN-1901', 'DD-Mon-YYYY') else 
trunc(customer.EVENT_START_DATE) END) AND to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '1-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '1-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '1-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '1-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') <= (CASE WHEN customer.EVENT_END_DATE is null THEN to_date('01-JAN-2101', 'DD-Mon-YYYY') else 
trunc(customer.EVENT_END_DATE) END));

-- Works fine
select contract.contract_id
from swcd.scd_contract contract 
left join SCD.COMPANY_TIMELINE_VIEW vendor on (vendor.company_id=contract.vendor_company_id and to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '1-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '1-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '1-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '1-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') >= (CASE WHEN vendor.EVENT_START_DATE is null THEN to_date('01-JAN-1901', 'DD-Mon-YYYY') else
trunc(vendor.EVENT_START_DATE) END) AND to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '1-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '1-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '1-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '1-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') <= (CASE WHEN vendor.EVENT_END_DATE is null THEN to_date('01-JAN-2101', 'DD-Mon-YYYY') else 
trunc(vendor.EVENT_END_DATE) END));

Please help me, Any answer would be appreciated.

Error report:
SQL Error: ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

Answer 1

select contract.contract_id
from swcd.scd_contract contract 

left join SCD.COMPANY_TIMELINE_VIEW customer on (customer.company_id=contract.customer_company_id and to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '01-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '01-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '01-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '01-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') >= (CASE WHEN customer.EVENT_START_DATE is null THEN to_date('01-JAN-1901', 'DD-Mon-YYYY') else 
trunc(customer.EVENT_START_DATE) END) AND to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '01-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '01-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '01-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '01-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') <= (CASE WHEN customer.EVENT_END_DATE is null THEN to_date('01-JAN-2101', 'DD-Mon-YYYY') else 
trunc(customer.EVENT_END_DATE) END) 
and contract.customer_company_id is not null)

left join SCD.COMPANY_TIMELINE_VIEW vendor on (vendor.company_id=contract.vendor_company_id and to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '01-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '01-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '01-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '01-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') >= (CASE WHEN vendor.EVENT_START_DATE is null THEN to_date('01-JAN-1901', 'DD-Mon-YYYY') else
trunc(vendor.EVENT_START_DATE) END) AND to_char(to_date(case when contract.ANNOUNCEMENT_DATE is null then case when contract.ANNOUNCEMENT_QUARTER='Q1' then '01-JAN-' when contract.ANNOUNCEMENT_QUARTER='Q2' then '01-APR-'
when contract.ANNOUNCEMENT_QUARTER='Q3' then '01-JUL-' when contract.ANNOUNCEMENT_QUARTER='Q4' then '01-OCT-' end || contract.ANNOUNCEMENT_YEAR else to_char(contract.ANNOUNCEMENT_DATE) end),'dd-MON-YYYY') <= (CASE WHEN vendor.EVENT_END_DATE is null THEN to_date('01-JAN-2101', 'DD-Mon-YYYY') else 
trunc(vendor.EVENT_END_DATE) END) 
and contract.vendor_company_id is not null);

Working fine, id column in join clause facing a null comparison issue. So added is not null in join clause and now working well.

Answer 2

It seems to be a bug in database. Anyway, try to replace '1-JUL-' with '01-JUL-' and so on. Your select uses implicit format conversion to_char(contract.ANNOUNCEMENT_DATE) in ELSE clause, this could be a problem too.

I would rewrite the select to avoid conversions as much as possible, like this

select contract.contract_id
from 
  (select contract_id, customer_company_id, vendor_company_id,
          nvl(ANNOUNCEMENT_DATE, 
              add_months(trunc(to_date(ANNOUNCEMENT_YEAR,'yyyy'),'yyyy'),
                 case when contract.ANNOUNCEMENT_QUARTER='Q1' then 0
                      when contract.ANNOUNCEMENT_QUARTER='Q1' then 3
                      when contract.ANNOUNCEMENT_QUARTER='Q1' then 6
                      when contract.ANNOUNCEMENT_QUARTER='Q1' then 9
                 end)) quarter_start
     from swcd.scd_contract) contract
left join SCD.COMPANY_TIMELINE_VIEW customer on
  (customer.company_id=contract.customer_company_id and
   contract.quarter_start between  nvl(trunc(customer.EVENT_START_DATE), to_date('01-JAN-1901', 'DD-Mon-YYYY')) AND
                                   nvl(trunc(customer.EVENT_END_DATE, to_date('01-JAN-2101', 'DD-Mon-YYYY')))) 
left join SCD.COMPANY_TIMELINE_VIEW vendor on
  (vendor.company_id=contract.vendor_company_id and
   contract.quarter_start between  nvl(trunc(vendor.EVENT_START_DATE), to_date('01-JAN-1901', 'DD-Mon-YYYY')) AND
                                   nvl(trunc(vendor.EVENT_END_DATE, to_date('01-JAN-2101', 'DD-Mon-YYYY'))));