Create a list of specific values ​​in CTE

Question

Is there a way to build a CTE for a list of hard-coded values? For example, I have a list of known identifiers (i.e. 101102,105,200 ...), how could I create a CTE with one column named ID, but all identification values ​​are hard-coded in the request? BTW, I need to run this query in Oracle. Thanks!

Answer 1

You can do something like

WITH cte AS ( SELECT 101 id FROM dual UNION ALL SELECT 102 FROM dual UNION ALL SELECT 105 FROM dual UNION ALL SELECT 200 FROM dual UNION ALL ... ) 

Depending on what you are really trying to execute, you might want to declare a collection and use it (with or without a function that parses a string separated by commas)

 CREATE TYPE num_tbl AS TABLE OF NUMBER; WITH cte AS ( SELECT column_value FROM TABLE( num_tbl( 101, 102, 105, 200 )) ) 

Answer 2

EDIT: The previously recommended solution only works for MSSQL. Therefore, I am adding an Oracle solution. I keep the original answer below.

I thought of another solution (although the one provided by Justin Cave still seems a little better) using temporary tables.

Here's how it might look like

 CREATE GLOBAL TEMPORARY TABLE temp_ids (id INT) ON COMMIT PRESERVE ROWS; INSERT INTO ids (id) VALUES (101); INSERT INTO ids (id) VALUES (102); INSERT INTO ids (id) VALUES (103); 

This should be a valid solution for an Oracle database.

Original answer below

---

I ran into a similar problem, and here is my solution (this does not work in Oracle DB, as mentioned in the comments, only MSSQL)

 WITH cte AS ( SELECT * FROM ( VALUES (1, 2, 3, ...), (2, 3, 4, ...) ) AS a (col1, col2, col3, ...) ) INSERT INTO ... 

Hope this helps :)