CODEWITHSUNDEEP
sqloracle-database
user1601238
user1601238

Reputation: 41

How to create a temporary table in Oracle SQL and add data to it?

I have built cohorts of accounts based on date of first usage of our service. I need to use these cohorts in a handful of different queries, but don't want to have to create the queries in each of these downstream queries.
Reason: Getting the data the first time took more than 60 minutes, so i don't want to pay that tax for all the other queries.

I know that I could do a statement like the below:

WHERE  ACCOUNT_ID IN ('1234567','7891011','1213141'...)

But, I'm wondering if there is a way to create a temporary table that I prepopulate with my data, something like

WITH MAY_COHORT AS ( SELECT ACCOUNT_ID  Account_ID, '1234567' Account_ID, '7891011' Account_ID, '1213141' )

I know that the above won't work, but would appreciate any advice or counsel here. thanks.

Upvotes: 1

Views: 301

Answers (2)

J. Chomel
J. Chomel

Reputation: 8395

You can also use a sub-select for your Ids (no WITH MY_COHORT):

WHERE  ACCOUNT_ID IN (
          SELECT Account_ID 
            from TableName "Where ... your condition to build your cohort ..." )

Upvotes: 0

Jimmy Smith
Jimmy Smith

Reputation: 2451

Unless I am missing something, you're already on the right track, just an adjustment to your CTE should work:

WITH MAY_COHORT AS ( SELECT Account_ID from TableName WHERE ACCOUNT_ID IN ('1234567','7891011','1213141'...) )

This should give you the May_Cohort table to use for subsequent queries.

Upvotes: 1

Related Questions