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

Question

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.

Jimmy Smith · Accepted Answer · 2014-02-19 18:10:19Z

1

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.

answered Feb 19, 2014 at 18:10

Jimmy Smith

2,4511 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

J. Chomel · Accepted Answer · 2016-03-31 12:54:46Z

0

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 ..." )

answered Mar 31, 2016 at 12:54

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related