Alternate to Loops and Read Statements in HANA procedures

[Former Member]

Hi Experts,

We have a client upgrading to HANA so there are many reports that we would like to code pushdown.

There are certain challenges that we are facing while creating procedures like:

The existing report has many loops and read statements inside so we are planning to bring that logic down inside Procedures.

As of now all the Select Queries have been incorporated via CE Functions inside the Procedures but the main challenge now is how to code for loop and read statements inside procedures.

I would like to put a loop on the output parameters below:

Also,Is it a good practice to remove all the loops and read statements and writing them in Procuderes or just leave them as it is and just put the Select Queries inside Procedures.

Below is the code that we want to have inside our procedures:

     LOOP AT i_vbfa INTO w_vbfa WHERE vbelv = w_vbak-vbeln.

       READ TABLE i_vbrk INTO w_vbrk WITH KEY vbeln = w_vbfa-vbeln

                                              fkart = 'ZRBC'.

       IF sy-subrc = 0.

         v_fkdat =  w_vbrk-fkdat.

         IF w_vbrk-zzbelnr IS INITIAL.

           lv_rbc = 'X'.

         ENDIF.

       ENDIF.

       READ TABLE i_vbrk INTO w_vbrk WITH KEY vbeln = w_vbfa-vbeln

                                                fkart = 'ZRBM'.

       IF sy-subrc = 0.

         lv_rbm = 'X'.

       ENDIF.

       CLEAR: w_vbfa,

              w_vbrk.

     ENDLOOP.

I guess we would have to use For and Cursor Statements but never used them.

Thanks,
Gaurav

[pfefferf]

Hi Gaurav,

think about joins and not loops .

Assuming that you have your data of internal tables i_vbfa and i_vbrk available as tables in the procedure you can join the data of the tables and "calculate" the necessary flag.

Just a quick pseudo coding for the first read (cause I have no dev environment here at the moment).

lt_result =

SELECT

t1.field1 as field1,

t2.field2 as field2,

CASE WHEN t2.field1 <> '' AND t2.zzbelnr = '' THEN 'X' ELSE '' END as RBC

FROM :i_vbfa as t1 LEFT OUTER JOIN :i_fkdat as t2

ON t1.vbeln = t2.vbeln

AND t2.fkart = 'ZRBC';

The field in the " t2.field1 <> '' " check in the CASE expression should be a field in table t2 (i_fkdat) which is always filled to check that really a data record exists in that table (because of the left outer join all entries of table t1 (i_vbfa) are returned.

Regarding the CASE expression you can have a look to chapter 1.6 of the HANA SQL reference guide (http://help.sap.com/hana/SAP_HANA_SQL_and_System_Views_Reference_en.pdf).

You can also use CE functions CE_PROJECTION, CE_JOIN and CE_CALC (with IF function) to reach this and to avoid the switch between the SQL Engine and Calc. Engine in your procedure (just check the infos in the HANA SQLScript guide http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf).

Best regards,

Florian

[Former Member]

Thanks Florian .