CODEWITHSUNDEEP
sqloracle-database
Kumar G
Kumar G

Reputation: 65

SQL In Clause Value

I have the following SQL

Select * 
from PS_VENDOR 
WHERE VENDOR IN ('111','222','333','444')

in which 444 VENDOR doesn't exist. but when I run the SQL I should get all 4 rows with all Vendors from IN CLAUSE and for 444 just populate BLANK or 'X'

I tried the following and it don't work 

Select * 
from PS_VENDOR 
WHERE NVL(VENDOR IN ('111','222','333','444'), 'X');

Upvotes: 2

Views: 79

Answers (1)

JohnHC
JohnHC

Reputation: 11205

Use a CTE to hold your values, left join

with V1 as
(
select '111' as VV from dual
union all
select '222' as VV from dual
union all
select '333' as VV from dual
union all
select '444' as VV from dual
)

select VV, PS_VENDOR.*

from V1
left join PS_VENDOR 
  on VENDOR = VV

Upvotes: 6

Related Questions