0

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
4
  • Do you really store the dates as VARCHAR and in DD-MM-YY format? – ypercubeᵀᴹ Feb 21 '18 at 17:45
  • Of course not.. – Nir Feb 22 '18 at 8:43
  • Then why is the VARCHAR(20) in this question? – ypercubeᵀᴹ Feb 22 '18 at 8:57
  • Because it's not relevent, change the date string to letters if you wish. – Nir Feb 22 '18 at 15:49
2

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   
2
  • Why not? There aren't other method? – Nir Feb 21 '18 at 16:23
  • this odcivarchar2list saved the day – Ricardo Pontual Jul 18 '19 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.