How to make INNER JOIN ON different fields by condition

Question

I need to make inner join by different conditions depending on the doc_type field value.

This is schema

Person (
  ...
  doc_type VARCHAR2(40),
  doc_series VARCHAR2(4),
  doc_number VARCHAR2(6),
  doc_date DATE
)

Document (
  id INTEGER,
  type VARCHAR2(40),
  series VARCHAR2(4),
  number VARCHAR2(6),
  date DATE
)

For example, if doc_type value is 01 I need to make join by doc_type and doc_series columns and if doc_type value is 02 then join should be done by doc_type, doc_series, doc_number and doc_date.

Something like this

SELECT d.* FROM document d
INNER JOIN person p ON
(CASE when d.doc_type = '01' then
  p.doc_type = d.type AND
  p.doc_series = d.series
      when d.doc_type = '02' then
  p.doc_type = d.type AND
  p.doc_series = d.series AND 
  p.doc_number = d.doc_number AND
  p.doc_date = d.doc_date
);

How can I do it in Oracle DB?

Answer 1

I think you need this

SELECT d.* 
FROM document d
INNER JOIN person p ON p.doc_series = d.series AND
( d.doc_type = '01' AND p.doc_type = d.type) OR
( d.doc_type = '02' AND p.doc_type = d.type AND p.doc_number = d.doc_number AND
  p.doc_date = d.doc_date)