急！急！oracle存储过程里，定义字符串后，赋值含有decode的会不支持呢，为什么呢，请赐教！

Question

 sqlstr:='
             select *
              from 
                (select a.default_line,a.mo_start_date,a.input_qty,decode(b.ErrorPinQty,null,0,b.ErrorPinQty)ErrorPinQty,
                round(decode(b.ErrorPinQty,null,0,b.ErrorPinQty)*1000000/decode(a.input_qty,null,1,a.input_qty),0) PPM
                from (select default_line,TRUNC(mo_start_date) mo_start_date,sum(input_qty) input_qty
                from t_pm_mo_base 
                where mo_start_date is not null
                group by default_line,TRUNC(mo_start_date)
                order by mo_start_date,default_line) a
                 left join(select TEST_LINE,TEST_TIME,count(serial_number) ErrorPinQty 
                from
                  (select distinct t.serial_number,t.TEST_LINE, trunc(t.TEST_TIME) TEST_TIME,/*MO_NUMBER,t.MODEL_NAME,t.SERIAL_NUMBER,*/t.SUPPLIER,
                  decode(c.ERROR_TYPE1,'0','屏','1','声音','2','画面')ERROR_TYPE1 
                  from t_re_repair t,t_co_error_code c
                  where t.test_code=c.error_code and c.error_type1=0
                  order by t.TEST_LINE,TEST_TIME)
                group by TEST_LINE,TEST_TIME
                order by TEST_LINE,TEST_TIME)b
                on a.default_line=b.TEST_LINE and a.mo_start_date=b.TEST_TIME
                where trunc(a.mo_start_date)=trunc(to_date('200812'||'26','yyyy-MM-dd'))
                order by a.mo_start_date,a.default_line)';

Answer 1

decode(c.ERROR_TYPE1,'0','屏','1','声音','2','画面')ERROR_TYPE1 
会提示：PROCEDURE EQHADMIN.P_T_LCD_MAJOR_REPORT 编译错误错误：PLS-00103: 出现符号 "0"在需要下列之一时：
       .(*@%&=-+;</>atinismodnotrem
          <an exponent (**)><> or != or ~=>=<=<>andorlikebetween||

Answer 2

根据你的问题 我作了测试declare sql_str varchar2(1000);
begin
  -- Test statements here
 sql_str:=' SELECT product_id,
       DECODE (warehouse_id, 1, 'Southlake', 
                             2, 'San Francisco', 
                             3, 'New Jersey', 
                             4, 'Seattle',
                                'Non domestic') 
       "Location of inventory" FROM OE.inventories
       WHERE product_id < 1775';
 execute immediate sql_str;
end;它也报相同的错误： 所以问题是你字符串里的单引号出错了： 改成：declare sql_str varchar2(1000);
begin
 sql_str:=' SELECT product_id,
       DECODE (warehouse_id, 1, ''Southlake'', 
                             2, ''San Francisco'', 
                             3, ''New Jersey'', 
                             4, ''Seattle'',
                                ''Non domestic'') 
       "Location of inventory" FROM OE.inventories
       WHERE product_id < 1775';
 execute immediate sql_str;
end;
 就不会报错。

Answer 3

oralce 字符串引号中不能再嵌套引号，要用chr(39)，结贴！