在我的代码中显示的表错误中已经存在缺少右括号和要添加的列

Question

我正在尝试运行此代码

declare 
temp_atr_val varchar2(400);
temp_val varchar2 (400);
temp_sum_percent decimal (10,3);
temp_variable number (10,3);
column_count number ; 
val_count number;
sales_store number;
begin    
select count(distinct ATTRIBUTE_INPUT) into column_count from look_up_table;
for ind in 1..column_count loop
    /* putting current value of attribute from look_up_table in temp variable*/
    select ATTRIBUTE_INPUT into temp_atr_val from (
        select ATTRIBUTE_INPUT, rownum rwn
        from 
        (
           select distinct ATTRIBUTE_INPUT
           from look_up_table
        )
    ) where rwn = ind;
    select count( value_for_atr ) into val_count from look_up_table;
    for ind in 1..val_count loop
   /* putting current value_for_atr for corresponding attribute from look_up_table in temp variable*/
        select value_for_atr into temp_val from 
         (
          select value_for_atr, rownum rwn
          from look_up_table
         ) where rwn = ind;
       SELECT SUM(CASE WHEN temp_atr_val = temp_val THEN net_sales_home ELSE 0 END) into temp_variable
       FROM schemafinal;
 /*temp_variable := temp_variable/sales_store;*/

 EXECUTE IMMEDIATE 'ALTER TABLE SAR ADD (percent_'||temp_val||' number)';
 EXECUTE IMMEDIATE ' update SAR b 
 set b.percent_'||temp_atr_val||'_'||temp_val||' = 105 ';

END LOOP;
END LOOP;   
END;

但每次代码显示以下两个错误中的一个：ORA-01430:正在添加的列已存在于表中

907. 00000-"缺少右括号"

当我检查SAR表时，它只生成了5个新列。我不知道为什么会发生这种事，我几乎什么都试过了。

Answer 1

在不了解数据结构的情况下很难分析查询。

但我假设您在第二个循环select查询中需要一个额外的ATTRIBUTE_INPUT条件来查找temp_val。否则，对于每个ATTRIBUTE_INPUT，temp_val将返回相同的结果。

这意味着在第一循环的第二次迭代中，temp_val将与第一次迭代的旧值相同。因此Alter表Add查询将抛出错误。

因此，请尝试添加额外的ATTRIBUTE_INPUT条件

   /* putting current value_for_atr for corresponding attribute from look_up_table in temp variable*/
        select value_for_atr into temp_val from 
         (
          select value_for_atr, rownum rwn
          from look_up_table
          where ATTRIBUTE_INPUT = temp_atr_val
         ) where rwn = ind;