Loop a result set and do some calcuation to get stock data

[Former Member]

Hi,

I am trying to simulate the stock calculation in R/3 using HANA solution for having a real time stock calculation using HANA. For this I replicated related tables to a HANA standalone system. Later I started to prepare a procedure to derive the summation logic combining the different tables involved in stock calculation.

I am stuck on the step where I need to do iteration on a result set and do summation and subtraction of defined movement types and later make a table which should be passed as the result set.I dont see any possible option to do such kind of runtime temporary tables and modification capability in the available HANA SQL script capabilities. I tried cursor, CE_UNION_ALL , various select based table variables to achieve this requirement.

I am pasting the attempted code below. Here I am trying to add some data and minus some data. Later I need to send the IBESTAND table as my result set. I dont see an option to send this as a select statement only...please throw some light...Thanks

eg:

FOR cur_row as cursor1 DO

V_MENGE := 0;

V_WERKS := CUR_ROW.WERKS;

V_LGORT := CUR_ROW.LGORT;

V_MATNR := CUR_ROW.MATNR;

SELECT

     "WERKS",

     "LGORT",

     "MATNR",

     "SHKZG",

     "MENGE"

INTO V_WERKS,

     V_LGORT,

     V_MATNR,

     V_SHKZG,

     V_MENGE

FROM :IWEG_MAT

WHERE "WERKS" = :V_WERKS

AND "LGORT" = :V_LGORT

AND "MATNR" = :V_MATNR

AND "SHKZG" = 'S';

V_ENDMENGE := 0;

V_ENDMENGE := CUR_ROW.LABST + CUR_ROW.INSME + CUR_ROW.SPEME + CUR_ROW.EINME + CUR_ROW.RETME - :V_MENGE;

V_MENGE := 0;

CNT := 1;

SELECT

     "WERKS",

     "LGORT",

     "MATNR",

     "SHKZG",

     "MENGE"

INTO V_WERKS,

     V_LGORT,

     V_MATNR,

     V_SHKZG,

     V_MENGE

FROM :IWEG_MAT

WHERE "WERKS" = :V_WERKS

AND "LGORT" = :V_LGORT

AND "MATNR" = :V_MATNR

AND "SHKZG" = 'H' ;

V_ENDMENGE := V_ENDMENGE + V_MENGE ;

V_BWKEY := CNT ;

CNT := CNT + 1 ;

V_CHARG := CNT ;

CNT := CNT + 1 ;  

INSERT

INTO IBESTAND VALUES ( :V_BWKEY,

     CUR_ROW.WERKS,

     CUR_ROW.MATNR,

     :V_CHARG,

     :V_ENDMENGE,

     :V_ANFMENGE,

     :V_MEINS,

     :V_ENDWERT,

     :V_ANFWERT,

     :V_SOLL,

     :V_HABEN,

     :V_SOLLWERT,

     :V_HABENWERT,

     :V_WAERS) ;

    END FOR;

[Former Member · Accepted solution]

Hi Tony,

Not everyone on SCN has experience with R/3. (Take me for example). Moreover, not many folks are included to go through many lines of someone else's code. You might get lucky, but in the meantime I'd recommend describing what the calculation is, give a simplified example with some test data and simplified code (i.e. just the required fields with understandable English words/abbreviations (for those die deutsche Abkuerzungen nicht so einfach verstehe))

I'd be happy to help with a bit of a cleaned up example.

[Former Member · Accepted solution]

Hi,

Giving an example:

I have a database table

Table1 with fields Plant, Material, Movement Types, Quantity.

Table2 with fields Plant, Material, Starting Quanity

Now I want to create a calculation view:

Logic : Table2.Starting Stock +- Table1.Movements (This can be Plus or Minus based on Movement types) for each material and Plant combination.

Here I need loop and temporary tables. How can we achieve such iterative solutions in HANA SQL script?..Is there an option to update data in internal tables / table types

Thank you

Happy Tony

[Former Member · Accepted solution]

If the logic is as simple as it sounds, quite a few ways to do it I would guess. I'm assuming Movement Type describes if it's positive or negative, and Quantity is always a positive value? Assuming so:

1) Create a generated column on Table1 that defines a field with appropriate positive/negative values

of Quantity.

2) UNION ALL with Table2, matching generated column above with Table2.Quantity

3) Aggregate, group by plant, material

This should get you the final quantity.

[Former Member · Accepted solution]

Hi Jody,

Thank you for the hint..It works for the example.

But in general do we have an internal table option in HANA script where we can use cursor based approach to iteratively fill it and sent as result set ?...

Thank you

Happy Tony

[Former Member · Accepted solution]

I'd recommend you read the SQLScript guide found at help.sap.com/hana_appliance

There you'll find details on usage of table variables, temporary tables, cursors, etc. Keep in mind that set-based processing is significantly faster than cursors, which should be avoided.