Best way to add calculated column in an Oracle CREATE TABLE .. SELECT statement

Works for me, and avoids calculating twice.  Thanks.

I suppose I should ask:
This isn't going to be used as a "temp" table for some other processing?

If so, Oracle doesn't work like other database products that encourage that sort of thing...  there are better alternatives!

Nope.  It's part of a monster Java/Flash UI with a wompload of graphs + Oracle Data Warehouse back-end build that has a wompload of 'dynamic features' in it.  For example, the above created table has a _MM_ for monthly aggregate numbers, _YY_ for yearly aggregate numbers, you get the idea.  My requirement is to create a column that is the sum of 1-9, so the super-dooper graphing UI can use that as a 'sum of the other columns' without having to bundle that into the query SQL.

You know best but that description sounds a lot like a table that is created "more than once, ever" for specific processing...

Jim,

Oracle supports "materialized views".  
Basically you create a view and the result is persisted automatically.

There aren't dead simple to use (but not too hard). Using these might be appropriate for this need.

Understand, but this place recently went from Materialized Views back to tables, even these tables are still prefixed with 'MV_', so the will is not there to create MV's.

I would strongly suggest you look at virtual columns.

I don't know how much data you are dealing with or the resources on the servers you have BUT....

Check out the example below.

The only physical storage in the new table is the cohort column.  Everything else you need is derived when queried.

3 outcomes from a performance aspect:
1: it will BLOW AWAY the performance of the current design
2: it will bog things down at query time so much the server grinds to a halt
3: No one noticed and they have cupcakes in the break room!!!

If the outcomes are 1 or 3, you will save a TON of storage space!!!

Nothing is free in this world so there is a catch.  It appears virtual columns cannot be used in a CTAS (or at least I couldn't get them to work) so it makes a 1 step process a 3 step process:  Create, insert then commit.

drop table tab1 purge;
create table tab1(cohorts number);

insert into tab1 values(2);
insert into tab1 values(6);
commit;

SELECT 
   pov_coh_Aa01,
   pov_coh_Aa02,
   pov_coh_Aa03,
   pov_coh_Aa04,
   pov_coh_Aa01+ pov_coh_Aa02+ pov_coh_Aa03+ pov_coh_Aa04 pov_total
from (
SELECT 
   case when bitand(coh.cohorts, 2) <> 0 then 1 else 0 end pov_coh_Aa01,
   case when bitand(coh.cohorts, 4) <> 0 then 1 else 0 end pov_coh_Aa02,
   case when bitand(coh.cohorts, 8) <> 0 then 1 else 0 end pov_coh_Aa03,
   case when bitand(coh.cohorts, 16) <> 0 then 1 else 0 end pov_coh_Aa04,
   case when bitand(coh.cohorts, 32) <> 0 then 1 else 0 end pov_coh_Aa05,
   case when bitand(coh.cohorts, 64) <> 0 then 1 else 0 end pov_coh_Aa06,
   case when bitand(coh.cohorts, 128) <> 0 then 1 else 0 end pov_coh_Aa07,
   case when bitand(coh.cohorts, 512) <> 0 then 1 else 0 end pov_coh_Aa08,
   case when bitand(coh.cohorts, 1024) <> 0 then 1 else 0 end pov_coh_Aa09
FROM tab1 coh
)
;



drop table tab1_virt purge;
create table tab1_virt
(
	cohorts number,
   	pov_coh_Aa01	generated always as (case when bitand(cohorts, 2) <> 0 then 1 else 0 end),
   	pov_coh_Aa02	generated always as (case when bitand(cohorts, 4) <> 0 then 1 else 0 end),
   	pov_coh_Aa03	generated always as (case when bitand(cohorts, 8) <> 0 then 1 else 0 end),
   	pov_coh_Aa04	generated always as (case when bitand(cohorts, 16) <> 0 then 1 else 0 end),
   	pov_coh_total	generated always as (
   		case when bitand(cohorts, 2) <> 0 then 1 else 0 end+
   		case when bitand(cohorts, 4) <> 0 then 1 else 0 end+
   		case when bitand(cohorts, 8) <> 0 then 1 else 0 end+
   		case when bitand(cohorts, 16) <> 0 then 1 else 0 end
	)
) 
/

insert into tab1_virt(cohorts) select cohorts from tab1;
commit;

select pov_coh_Aa01, pov_coh_Aa02, pov_coh_Aa03, pov_coh_Aa04, pov_coh_total from tab1_virt;
  	

Select allOpen in new window