Full outer join with a list in oracle

Question

I have a table test:

id    number
name  varchar2(20)
ddate varchar2(20)

I want to select all row from test on specepic date and full outter join with a list of values. For example let's say this is the data:

SQL> select * from test;

id     name     ddate
---    ----     ------
1      a        20-02-18
2      b        21-02-18
3      c        21-02-18
4      d        22-02-18

I want this result:

SQL> --of course this doesn't work.
SQL> select * 
from test full outter join ('b','e') list on test.name=list.name 
where ddate='20-02-18';

id     name     ddate
---    ----     ------
null   e        null
2      b        21-02-18
3      c        21-02-18

Answer 1

Not something I would recommend doing, but:

select test.id, nvl(test.name, list.column_value) as name, test.ddate
from
  test 
  full outer join table(sys.odcivarchar2list('b', 'e')) list
on test.name = list.column_value
where nvl(ddate, date'2018-02-21') = date'2018-02-21'
order by id nulls first
;

        ID NAME       DDATE               
---------- ---------- --------------------
           e                              
         2 b          21-FEB-18           
         3 c          21-FEB-18