Oracle- create a temporary resultset for use in a query

Question

How do I create a temporary result set for use in an SQL without creating a table and inserting the data?

Example: I have a list of, say 10 codes for example. I want to put this into a query, and then query the database to see which codes in this temporary list do not exist in a table.

If it was already in a table, I could do something like:

SELECT
  ITEM_CODE
FROM
  TEMP_ITEMS
MINUS
SELECT
   ITEM_CODE
FROM
   M_ITEMS

Is their a way without using PL/SQL, and pure SQL to create a temporary rowset before querying? Please don't answer with something like:

SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL

I am sort of thinking of something where I can provide my codes in an IN statement, and it turns that into rows for use in a later query.

Edit: so everyone knows my objective here, basically I sometimes get a list of product codes that I need to find which ones in the list are not setup in our system. I want a quick way to throw this into an SQL statement so I can see which ones are not in the system (rather than importing data etc). I usually put these into excel, then do a formula such as :

="'"&A1&"',"

So that I can create my comma separated list.

Answer 1

If you are using oracle 11g you can do this

with t as 
(
 select (column_value).getnumberval() Codes from xmltable('1,2,3,4,5')
)
SELECT * FROM t
WHERE NOT EXISTS (SELECT 1 FROM M_ITEMS M WHERE codes = M.ITEM_CODE);

or

with t as 
(
 select (column_value).getstringval() Codes from xmltable('"A","B","C"')
)
SELECT * FROM t
WHERE NOT EXISTS (SELECT 1 FROM M_ITEMS M WHERE codes = M.ITEM_CODE);

Answer 2

I would go with:

with t as (
    select 1 as val from dual union all
    select 2 as val from dual
)
select . . .

And then use "t" or whatever you call it, in the subsequent query block.

I'm not sure what the objection is to using the select method . . . just pop the values you want in a column in Excel and produce the code for each value by copying down the formula. Then paste the results back into your query interface.

If you want to use a temporary table, you can use the values clause. Alternatively, you can use string functions if you only want IN functionality. Put the values in a comma separated list and check to see if it matches a particular value:

where ','||<list>||',' like '%,'||col||',%'