Q: Insert statement in variable declaration has error PLS-00103 (when using case) I'm using the below insert statement to declare v_text.
also i am using case in the select statement. But i am getting the below error in the case statement.
Error(469,119): PLS-00103: Encountered the symbol "VALUE" when expecting one of the following:     * & = - + ; < / > at in is mod remainder not rem    <an exponent (**)> <> or != or ~= >= <= <> and or like like2    like4 likec between || member submultiset 

 v_text := 'INSERT INTO temp_value '||
           '(ach_id, language, value, value_ivi, dq_nr, unit, leervalue, gg_id)'||
           'SELECT '||p_ach_id||', d.lfd, w.sprache,'||
            case when y.zeige_unit = 1 then regexp_replace(w.value,'(\d+\.\d+)','\1'||w.unit)  else 
             w.value end value,||
            'w.value, w.value_ivi, '||i||', w.unit, w.leervalue, w.gg_id '||
           ' FROM ZUL_GEN_NEU_Zwischenvaluee_value w
           Join DV_FORMAT_POS_DATENQ y
              ON w.dq_id = y.for_pos_dat_id,'||
           ' (SELECT /*+ no_merge(v) */ DISTINCT v.lfd'||
                  ' FROM   '||
                  '( '||p_vv_text||') v'||
                  ' JOIN ('||v_def_text||') d '||v_def_where||') d '||
           ' WHERE w.ach_id = '||p_ach_id||
           ' AND w.pos_id = '||p_pos_id||;


A: I don't have all of your variables, but as a start:
 v_text := 'INSERT INTO temp_value '||
           '(ach_id, language, value, value_ivi, dq_nr, unit, leervalue, gg_id)'||
           'SELECT '||p_ach_id||', d.lfd, w.sprache,'||
            case when y.zeige_unit = 1 then regexp_replace(w.value,'(\d+\.\d+)','\1'||w.unit)  else 
             w.value end ||  -- removed "value,"
            'w.value, w.value_ivi, '||i||', w.unit, w.leervalue, w.gg_id '||
           ' FROM ZUL_GEN_NEU_Zwischenvaluee_value w
           Join DV_FORMAT_POS_DATENQ y
              ON w.dq_id = y.for_pos_dat_id,'||
           ' (SELECT /*+ no_merge(v) */ DISTINCT v.lfd'||
                  ' FROM   '||
                  '( '||p_vv_text||') v'||
                  ' JOIN ('||v_def_text||') d '||v_def_where||') d '||
           ' WHERE w.ach_id = '||p_ach_id||
           ' AND w.pos_id = '||p_pos_id; -- fixed "||;"

(I set the SO language to Lua as it seems to display quoted text better.)
