Menu
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!
532
You can create lists of SQL Query or Fixed Data values . In the Data Model components pane, click List of Values and then click Create new List of Values. Enter a Name for the list and select a Type.
Advantage of Using CTE Instead of having to declare the same subquery in every place you need to use it, you can use CTE to define a temporary table once, then refer to it whenever you need it. CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries.
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 :)
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
