Error at update statement in AMDP procedure

[avinashd_m]

Hi All,

I have created the AMDP procedure as follows:

METHOD my_hana_procedure by database procedure

                           for hdb

                           language sqlscript.

lt_temp_tab = select *from :i_view;     -----> copying an entire data from importing parameter i_view to temporary table lt_temp_tab 

begin

     declare cursor c_cth_data for     -----> Declaring cursor to loop through result set of table

      select * from :lt_temp_tab;

       for r1 as c_cth_data do

         update :lt_temp_tab                    -----> Here we are getting an error (some weird error as SQL script error : &a1&A2&a3&A4).

          set S_FPCA = (r1.S_FTCA * 0.014)   -----> logic here is to update row field of a temporary table to some new value

             where S_FRES = r1.S_FRES;

       end for;

end;     

ENDMETHOD.

instead of importing temporary table if i use any other table like SFLIGHT i will not get any error.

Is there any restriction that we cannot use update for temporary table (lt_temp_tab) as explained above in procedure in AMDP procedure?

Please suggest me here .

Thanks in advance,

Avinash

[avinashd_m]

Hi All,

I have created the AMDP procedure as follows:

METHOD my_hana_procedure by database procedure

                           for hdb

                           language sqlscript.

lt_temp_tab = select *from :i_view;     -----> copying an entire data from importing parameter i_view to temporary table lt_temp_tab 

begin

     declare cursor c_cth_data for     -----> Declaring cursor to loop through result set of table

      select * from :lt_temp_tab;

       for r1 as c_cth_data do

         update :lt_temp_tab                    -----> Here we are getting an error (some weird error as SQL script error : &a1&A2&a3&A4).

          set S_FPCA = (r1.S_FTCA * 0.014)   -----> logic here is to update row field of a temporary table to some new value

             where S_FRES = r1.S_FRES;

       end for;

end;     

ENDMETHOD.

instead of importing temporary table if i use any other table like SFLIGHT i will not get any error.

Is there any restriction that we cannot use update for temporary table (lt_temp_tab) as explained above in procedure in AMDP procedure?

Please suggest me here .

Thanks in advance,

Avinash

[pfefferf]

Hello Avinash,

update on local defined tables is not supported.

As you loop over a cursor (which causes bad performance) and wanna update the S_FPCA column of each single row with a calculated value only, you can do that calculation already in the query.

lt_temp_tab = select (S_FTCA * 0.014) as S_FPCA, ... other columns ... FROM :I_VIEW;

Regards,

Florian

[avinashd_m]

Hi Florian ,

Thanks for the inputs . It worked for me.

Regards,
Avinash