replacing existing column

Database
Enthusiast

replacing existing column

Hello,

In the below query, i get the error 

CREATE TABLE Failed. 3515:  Duplication of column PRCD_AMT in a table, derived table, view, macro or trigger. 

I understand that is cause i am doing select * and then creating the field prcd_amt again in the case statement. One option is replace the select * with the specific column names , but this table has hundreds of columns so not viable. 

Another option would be to create a new field say prcd_AMT2. Then drop procd_amt. Then rename prcd_amt2 to prcd_amt. 

IS there a more optimal way of doing this? 

Please advice. Thanks. 

create multiset volatile table all_apps2 as 

(

select a.*, 

case when prcd_amt is null and od_lmt > 0 then od_lmt end as prcd_amt

from all_apps1 a

)

with data and stats

on commit preserve rows

;

2 REPLIES
Enthusiast

Re: replacing existing column

You can create the all_apps2 same as all_apps1 and write an update statement for prcd_amt

Or you could just use 'HELP TABLE all_apps1' to get the column list and copy paste it in the Select statement and add the transformation as required.

Enthusiast

Re: replacing existing column

Thanks Sachin. I will go with the second option as then I can do it all in one query.