Hi. I am trying to join between 2 tables in sql and oddly am getting a ora-01841 error but only when I add a 2nd where condition.
I have a table called xxsac_disco_full_all which has various start and end dates like person effective dates and contract end dates (all of these are of 'DATE' type)
I also have a table called xxsac_dor_discipline which has various dates (again all DATE type).
The common join is person_id.
If I write
select dor.*
from xxsac_dor_discipline dor, xxsac_disco_full_all disco
where dor.person_id = disco.person_id
and dor.date_investigation_started between disco.effective_start_date and disco.effective_end_date
Then it works fine.
If I write
select dor.*
from xxsac_dor_discipline dor, xxsac_disco_full_all disco
where dor.person_id = disco.person_id
and dor.date_investigation_started between disco.contract_start_date and disco.contract_end_date
It also works fine
But if I try and do it with both those where clauses together:
select dor.*
from xxsac_dor_discipline dor, xxsac_disco_full_all disco
where dor.person_id = disco.person_id
and dor.date_investigation_started between disco.effective_start_date and disco.effective_end_date
AND dor.date_investigation_started between disco.contract_start_date and disco.contract_end_date
Then I get a ora-01841 (full) year must be between -4712 and +9999, and not be 0 error
I just don't understand why each individual where clause would work fine but when I try and combine them it fails?
Any ideas?
Thanks
Martin