CODEWITHSUNDEEP
sqloracle-database
Jono
Jono

Reputation: 13

Return default value for some IN condition not match

I think this shouldn't be really hard. I am writing an Oracle-SQL code to extract data from SQL:

select ID, Qty from TableOne where ID in ('A', 'B', 'C')

I want the database to show the result of the query if there is match for some items in the IN condition, and return a default value if there is no match for those items in the IN condition. For example, I want the result to be:

+----+-----------+
| ID |    Qty    |
+----+-----------+
| A  | 3         |
| A  | 5         |
| B  | 4         |
| C  | Not Found |
+----+-----------+

Where there is no ID = C in the table TableOne.

Is there any easy way to code the result? Thank you very much!

Upvotes: 1

Views: 1443

Answers (3)

Pankaj_Dwivedi
Pankaj_Dwivedi

Reputation: 580

if this works for you:

   select t2.ID, case when t2.QTY is NULL then TO_CHAR('Not found') else t2.QTY end "QTY" from TableOnet1 t1 right join Tabletwo t2
on t1.ID = t2.ID where t2.ID in ('A', 'B', 'C')

Upvotes: 0

MT0
MT0

Reputation: 168681

Use COALESCE, NVL or CASE with a LEFT OUTER JOIN and specify the ids in a sub-query factoring clause:

WITH ids_to_match( id ) AS (
  SELECT 'A' FROM DUAL UNION ALL
  SELECT 'B' FROM DUAL UNION ALL
  SELECT 'C' FROM DUAL
)
select i.ID,
       COALESCE( TO_CHAR(Qty), 'Not Found' ) AS Qty
from   ids_to_match i
       LEFT OUTER JOIN TableOne t
       ON ( t.id = i.id )

or use a collection and a table collection expression:

select i.COLUMN_VALUE AS ID,
       COALESCE( TO_CHAR(Qty), 'Not Found' ) AS Qty
from   TABLE( SYS.ODCIVARCHAR2LIST( 'A', 'B', 'C' ) ) i
       LEFT OUTER JOIN
       TableOne t
       ON ( t.id = i.COLUMN_VALUE )

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use LEFT JOIN with UNION ALL :

WITH ALL_ID AS (
     SELECT 'A' AS ID FROM DUAL UNION ALL
     SELECT 'B' AS ID FROM DUAL UNION ALL
     SELECT 'C' AS ID FROM DUAL 
)
SELECT A.ID, t.Qty -- REPLACE NULL WITH NOT FOUND
FROM ALL_ID A ID LEFT JOIN
     Table t
      ON t.ID = A.ID;

Upvotes: 2

Related Questions