Return default value for some IN condition not match

Question

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!

Hi Jono, I don't think you will be able to achieve what you want using an IN operator. What you will need to do is to do an outerjoin between TableOnbe and another table/temp table which has the values (A, B, C). You can also use the NVL2 function as outlined here. https://stackoverflow.com/questions/3523036/what-is-the-oracle-equivalent-of-sql-servers-isnull-function — Francis, Mar 02 '20 at 08:57
@Francis ah... I understand what you mean. But how to add a temp table in Oracle SQL? I'm just a newbies in the SQL world. — Jono, Mar 02 '20 at 09:31
Does this answer your question? [Oracle SQL - IN Clause display all record given in IN condition even when data not present in table](https://stackoverflow.com/questions/46527785/oracle-sql-in-clause-display-all-record-given-in-in-condition-even-when-data-n) — Ponder Stibbons, Mar 02 '20 at 11:50
Hey Jono, I was in a train when I answered this question. Looks like there's been a few responses. I hope one of them answered your question — Francis, Mar 02 '20 at 17:34

MT0 · Answer 1 · 2020-03-02T08:46:04.607

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 )

score 2 · Answer 2 · answered Mar 02 '20 at 08:39

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;

Pankaj_Dwivedi · Answer 3 · 2020-03-02T09:31:41.017

0

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')

edited Mar 02 '20 at 09:31

answered Mar 02 '20 at 09:01

Pankaj_Dwivedi

565
4
15

this one not ok. It can show only the first three rows, as there is ID = A and ID = B in the table – Jono Mar 02 '20 at 09:18
Yes... Need to add left join or right join for that. You didn't mention you had two tables. Updating my ans. – Pankaj_Dwivedi Mar 02 '20 at 09:27
There is only one table, TableOne. In that table, the ID column has A, B, D, E, F, ..., Z. Except C. But I want the result to tell me there is no C if I try to query the Qty data of ID = C. – Jono Mar 02 '20 at 09:37

Return default value for some IN condition not match

3 Answers3