All Forums Database
SGIT 8 posts Joined 08/16
01 Sep 2016
replacing existing column



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



02 Sep 2016

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.

SGIT 8 posts Joined 08/16
02 Sep 2016

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

You must sign in to leave a comment.