Oracle- create a temporary resultset for use in a query
oracle, sql
Solution
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);
Problem
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.