CODEWITHSUNDEEP
sqloracle-database
Prabhat Kumar
Prabhat Kumar

Reputation: 21

Return list of supplied values that are not in table in Oracle

There is an EMPLOYEE table as below

EPM_ID
------
1001
1002
1004

And I have a list of EPM_IDs which I want to validate: (1000, 1001, 1002, 1003, 1004, 1005)

How to write Oracle SQL query only (without creating any temporary table) to return list of EPM_IDs that are not in the EMPLOYEE table, but are in my list? For instance, the result should be 1000, 1003, 1005.

Upvotes: 0

Views: 1092

Answers (4)

Prabhat Kumar
Prabhat Kumar

Reputation: 21

So, here is the solution which saved my day. I had restrictions to create temporary table in production DB which is a simple and obvious solution to solve this problem.

SELECT column_value AS EMP_ID 
FROM   TABLE(sys.Odcinumberlist(1000, 1001, 1002, 1003, 1004, 1005)) 
MINUS 
SELECT emp_id 
FROM   employee

Explanation : table(sys.odcinumberlist(1000, 1001, 1002, 1003, 1004, 1005)) converts the supplied values to a table like structure with single column named EMP_ID like below. Then normal SQL MINUS operation is applied on it.

EMP_ID
------
1000
1001
1002
1003
1004
1005

(Note : sys.odcinumberlist() has limitation to take maximum 999 arguments)

Now don't ask me what exactly sys.odcinumberlist() creates internally. I am not a DB professional. Please refer http://www.dba-oracle.com/t_advanced_sql_table_expressions.htm

Upvotes: 0

Suddhasatwa Bhaumik
Suddhasatwa Bhaumik

Reputation: 47

Can you please let us know the Oracle database version which you are using and the output of DESC command of the Employee Table?

Also, please let us know the number of rows in your table (I.e., existing no of rows in the table) and the length/size of the list of employee IDs which you're trying to find/not-find from your table?

Also, this list of employee IDs which you have : are they stored in a File, or another table, or in a webpage, etc.? Kindly advise.

This will help answer your question further.

Regards!

Suddhasatwa

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can use connect by clause to convert your list to rows and then do MINUS operation as follows:

SQL> -- GENERATING SAME DATA AS YOUR TABLE
SQL> WITH YOUR_DATAA (EMPLOYEE_ID) AS
  2  (SELECT '1001' FROM DUAL UNION ALL
  3  SELECT '1002' FROM DUAL UNION ALL
  4  SELECT '1004' FROM DUAL),
  5  -- YOUR QUERY STARTS FROM HERE -- WITH
  6  YOUR_LIST (LST) AS
  7  (SELECT '1000, 1001, 1002, 1003, 1004, 1005' FROM DUAL)
  8  --
  9  SELECT TRIM(REGEXP_SUBSTR(LST, '[^,]+', 1, LEVEL)) AS EMPLOYEE_IDS
 10  FROM YOUR_LIST
 11  CONNECT BY LEVEL <= REGEXP_COUNT(LST, ',') + 1
 12  MINUS
 13  SELECT EMPLOYEE_ID
 14  FROM YOUR_DATAA;

EMPLOYEE_IDS
--------------------------------------------------------------------------------
1000
1003
1005

SQL>

Cheers!!

Upvotes: 0

zip
zip

Reputation: 4061

This will do it:

select EPM_IDs 
from
(
  select 1000 as EPM_IDs union 
  select 1001 union 
  select 1002 union 
  select 1003 union 
  select 1004 union 
  select 1005
)a
where not exists(select 1 from EMPLOYEE e where a.EPM_IDs = e.EPM_IDs)

Upvotes: 1

Related Questions