create new column [message #444861] |
Wed, 24 February 2010 10:01  |
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   |
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   |
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 #444866 is a reply to message #444865] |
Wed, 24 February 2010 10:14   |
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 #444868 is a reply to message #444866] |
Wed, 24 February 2010 10:17   |
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   |
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.
|
|
|
|