Add column before another

[SaintJames]

Can someone help me with this. I'm triing to conver a sql statement from MS-SQL to oracle

All the statement does in add a column before and other, I am unsure about the correct syntax for Oracle.

here is my MS-SQL statement:
ALTER TABLE tbl_a ADD field_B NUMERIC(8) NULL BEFORE field_C ROUTID;

I know I have to change the value to NUMBER but the BEFORE part is not recognized

ALTER TABLE tbl_a ADD field_B NUMBER NULL BEFORE field_C ROUTID;


Thank you

 

[alexhu]

Oracle adds the columns to the 'end'.

There is no before - why would you need to use it ?

If you want columns in a particular order in your result, then just select them in that order.

As you can see MS-Sql is not Oracle

Alex

 

[Turkbear]

Hi,
And if you MUST have the columns in a particular order, create a new table using the:
Create table new_table as select col_I_want_first,
select col_I_want_second,
select col_I_want_third,
select col_I_want_fourth, etc..

from Original_table;

Rename Original_table to Original_table_backup;
Rename new_table to original_table;

Enjoy...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[alexhu]

Just thought of a reason why you would want to create a table with the columns in a certain order.....

If you create them alphabetically, then the 'describe' statement becomes a lot easier to look through

But apart from that.............?

Alex

 

[SantaMufasa]

BTW, James, if your initial response to Alex's and Turkbear's correct responses is, "Why wouldn't Oracle allow me to place a column wherever I want amongst the other columns in the table?" consider the implications of such a "feature": If 10 million rows already exist in the table that you want to modify, if you add a new column amongst existing columns, then Oracle must reorganise all 10 million rows in the table, placing a null column amongst the existing columns.

Alternatively, Oracle adds all new columns at the end of the existing table structure. Since Oracle does not need to store trailing NULL columns in a row, then using this modification scheme/restriction, the structural modification has absolutely no impact on existing rows; the only impact is a single-row update in the data dictionary to the structural definition of your table.

Does Oracle's method of choice in this matter now make a bit more sense?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.