We help IT Professionals succeed at work.
Get Started

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

Jim Horn
Jim Horn asked
on
256 Views
Last Modified: 2015-12-10
(Warning:  SQL Server expert, Oracle N00b)

Hello Oracle experts

I have a requirement to build on the currently-in-production (mocked up) code below, adding a column pov_coh_count and populating it with the sum of columns pov_coh_Aa01 through .._Aa09.

CREATE TABLE TARGET PCTFREE 0 NOLOGGING AS
SELECT Column1, Column2, Column3, ColumnN, 
   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 SomeTable 
LEFT OUTER JOIN SomeOtherTable
LEFT OUTER JOIN YetAnotherTable coh
LEFT OUTER JOIN YouGetTheIdea

Open in new window


What would you recommend as the best way?  Calculated column in the SELECT, add the column after the above statement and then UPDATE, ...

Thanks.
Jim
Comment
Watch Question
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE