CODEWITHSUNDEEP
sqloracle-databasecommon-table-expression
mickey_tx
mickey_tx

Reputation: 475

Build a list of defined values into CTE

Is there any way to build a CTE for a list of hard coded values? For example, I have a list of known IDs (i.e. 101,102,105,200...), how would I be able to create a CTE with one column called ID but all the ID values are hard coded in the query? BTW, I need to run this query in Oracle. Thanks!

Upvotes: 33

Views: 56305

Answers (4)

Graham Laight
Graham Laight

Reputation: 4840

Slight improvement to Justin Cave's popular answer (works in SQL Server and PostgreSQL, may not work in Oracle):

WITH cte (num) AS 
(
  SELECT 101 UNION SELECT 102 UNION SELECT 105 UNION SELECT 200
)

select num from cte

Upvotes: 1

VLRoyrenn
VLRoyrenn

Reputation: 786

You can use collection types for this, either submitting them as a bound parameter or creating them within your query.

As Justin Cave suggested, you can create your own collection type, but SYS contains a few for basic types that are defined by default, such as SYS.ODCIDATELIST (for DATE), SYS.ODCINUMBERLIST (for NUMBER/NUMERIC), SYS.ODCIVARCHAR2LIST (for VARCHAR2 up to 4000 chars), among those that don't seem specifically made for internal use.

Since you're using integer IDs, the built-in numeric collection sould work just fine:

-- column_value and table() work similarily to UNNEST() in Postgres
SELECT column_value as selected_id FROM TABLE(
    SYS.ODCINUMBERLIST(101, 102, 105, 200)
)

Depending on what your host language supports, you can also send the list as a parameter instead. In Python, for instance, using cx_Oracle directly:

import cx_Oracle

query = 'select column_value as selected_id FROM TABLE(:id_list)'

conn = cx_Oracle.connect('user', 'hunter2', '//192.0.2.5:1521/mydb')

OdciNumberList = conn.gettype("SYS.ODCINUMBERLIST")
id_list = OdciNumberList.newobject()

id_list.extend([101, 102, 105, 200])

cur = conn.cursor()
res = cur.execute(query, id_list=id_list )
res.fetchall() # [(101,), (102,), (105,), (200,)]

Then you just need to wrap it in a CTE.

Upvotes: 3

Zax
Zax

Reputation: 698

EDIT: previously advised solution works only for MSSQL. Therefore I am adding an Oracle solution. I am keeping the original answer below.

I thought of one more solution (though the one provided by Justin Cave still seems a bit better) - using temporary tables.

Here is how it may 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 Oracle database.

Original answer below

I have come across similar issue and here is my solution (this does not work on Oracle DB as mentioned in comments, only MSSQL though)

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

Hope this helps :)

Upvotes: 49

Justin Cave
Justin Cave

Reputation: 231681

You could 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 accomplish, though, you may want to declare a collection and use that (with or without a function that parses a comma-separated string)

CREATE TYPE num_tbl
    AS TABLE OF NUMBER;

WITH cte AS (
  SELECT column_value
    FROM TABLE( num_tbl( 101, 102, 105, 200 ))
)

Upvotes: 23

Related Questions