Home » SQL & PL/SQL » SQL & PL/SQL » create new column
create new column [message #444861] Wed, 24 February 2010 10:01 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Hi,

I want to add a variable number of new columns to an existing table temp (with column prodid).

Example:

NewColumnNo = 4
-> the columns shall be named rate_1, rate_2, rate_3 and rate_4
-> the values shall be = Column / NewColumnNo
The result shall be like this:

create table temp_res (prodid integer, rate_1 number, rate_2 number, rate_3 number, rate_4 number);
insert into temp_res values(1, 0.25, 0.5, 0.75, 1);
insert into temp_res values(2, 0.25, 0.5, 0.75, 1);
insert into temp_res values(3, 0.25, 0.5, 0.75, 1);
insert into temp_res values(4, 0.25, 0.5, 0.75, 1);
insert into temp_res values(5, 0.25, 0.5, 0.75, 1);
insert into temp_res values(6, 0.25, 0.5, 0.75, 1);
insert into temp_res values(7, 0.25, 0.5, 0.75, 1);
insert into temp_res values(8, 0.25, 0.5, 0.75, 1);
insert into temp_res values(9, 0.25, 0.5, 0.75, 1);


I need a solution which works ror any number of new columns. How can I do this?

Thanks, Stefan

[Updated on: Wed, 24 February 2010 10:05]

Report message to a moderator

Re: create new column [message #444863 is a reply to message #444861] Wed, 24 February 2010 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Some dynamic sql will do the job, but seriously - why do you want to do this?
Dynamically changing table structures just makes your life hard - believe me I've seen it attempted several times.
Just store your data in third normal form - have the rates in a seperate table so each rate is another row rather than another column. It'll make your life so much easier in the long run.
Re: create new column [message #444864 is a reply to message #444861] Wed, 24 February 2010 10:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By the pricking of my thumbs, a bad application design this way comes....

If you are routinely adding and removing columns from your tables, your application is badly written.

If you really need to do this then you just need to write a bit of pl/sql that loops throught from 1-no. columns and does
EXECUTE IMMEDIATE 'ALTER TABLE <tab_name> ADD( '||col_name||' number default '||<default value||')';
Re: create new column [message #444865 is a reply to message #444864] Wed, 24 February 2010 10:13 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
Is there a solution in the model clause, whith iterate or for-loops for example?

[Updated on: Wed, 24 February 2010 10:13]

Report message to a moderator

Re: create new column [message #444866 is a reply to message #444865] Wed, 24 February 2010 10:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I wouldn't think so - you need to do dynamic DDL, so your solution is going to be pl/sql based.

Why would you want to make things more complex than they need to be?
Re: create new column [message #444867 is a reply to message #444861] Wed, 24 February 2010 10:16 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
EXECUTE IMMEDIATE + For loop
Re: create new column [message #444868 is a reply to message #444866] Wed, 24 February 2010 10:17 Go to previous messageGo to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
I know that this example seems quite silly, but it is only a simplification of a much more complex issue. Any solution in the model clause?
Re: create new column [message #444870 is a reply to message #444868] Wed, 24 February 2010 10:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No.

The model clause allows you to generate complex result sets using methods that cannot be duplicated in SQL otherwise.

It will not do dynamic DDL.

Possibly if you gave us some idea of what the problem is, and why you think you need the MODEL clause, we might be able to help.
Re: create new column [message #444872 is a reply to message #444861] Wed, 24 February 2010 10:35 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a real test case of what you have and what you want in the end.

Regards
Michel
Previous Topic: Find two or more consecutive dates
Next Topic: Calculate with calculated fields
Goto Forum:
  


Current Time: Tue Feb 11 16:57:47 CST 2025