Array usage in AMDP table function..

[former_member375669]

Hello Experts,

i have a requirement in which i have to use values of first record to get calculated value in second record. Please look the Sc to get exact requirement.

refereed this blog https://blogs.sap.com/2014/12/16/using-array-as-internal-table-to-handle-and-process-data/comment-pa...

and used array for calculation. but unable to calculate the value...

using below code

for stock in 1 .. CARDINALITY(:a_mandt ) do

a_quantity[:stock] + a_quantity[:stock + 1];

end for;

but getting error "SQLSCRIPT message: identifier must be declared: A_QUANTITY &A0&A1&A2&A3&A4&A5&A6&A7&A8&A9"

please Suggest!!

i m pretty much open to take another approach to solve the issue.

Thanks in advance..capture.png

[pfefferf]

Not knowing your whole code, but the error message looks like the array A_QUANTITY is not declared. Please check this.

The other question is why you need that construct and why it cannot be solved with pure SQL? Again, as there is not information about the required result, no deailed advice can be given. But from the code snippet it looks like it can be done with SQL only.

If really such an imperative logic is required, maybe it is also worth to take a look to Index-based cell access to Table Variables. With that no conversion between arrays and table variables needs to be done.

Regards,
Florian

[former_member375669]

Thanks Florian for your input,

requirement is straight forward i need to deduct the quantity from current stock in recursive manner. leftover stock from first line will be considered as current stock for second line (please ref excel snapshot i attached in question.).

now yes if it is possible with SQL please suggest that also. Its my first CDS having vary basic knowledge.

i am attaching complete code for your ref.

 CLASS ZMM_CL_TEST_AMDP DEFINITION
  PUBLIC
  FINAL
  CREATE PUBLIC .
  PUBLIC SECTION.
* Marker interface for Database Procedures
  INTERFACES: if_amdp_marker_hdb.
*  TYPES: TY_TABLE TYPE zmmistockaging.
*  data: lv_table type table of zmmistockaging.
  TYPES: TY_TABLE TYPE zmmistockaging.
         TYPES: BEGIN OF ty_rank,
                 MANDT     type zmmistockaging-mandt,
                 MATBF     type zmmistockaging-matbf,
                 WERKS     type zmmistockaging-WERKS,
                 CHARG_SID type zmmistockaging-CHARG_SID,
                 BUDAT     type zmmistockaging-BUDAT,
                 MAT_KDAUF type zmmistockaging-MAT_KDAUF,
                 MAT_KDPOS type zmmistockaging-MAT_KDPOS,
                 LGORT_SID type zmmistockaging-LGORT_SID,
                 STOCK_QTY type zmmistockaging-STOCK_QTY,
                 STOCK_BALANCE  type zmmistockaging-STOCK_BALANCE ,
                 AGING         type zmmistockaging-AGING,
                 RANK type i,
                END OF ty_rank.
  DATA: lv_table type table of zmmistockaging.
* Method Definition
class-methods get_agging_details for table function ZMM_AGGING_CALCULATION.
    .
  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.
CLASS ZMM_CL_TEST_AMDP IMPLEMENTATION.
METHOD get_agging_details BY DATABASE FUNCTION
                         FOR HDB
                         LANGUAGE SQLSCRIPT
                         OPTIONS READ-ONLY
                         USING zmmistockaging.  /*its my CDS view */
   

   DECLARE lv_index int;
   DECLARE LINDEX DECIMAL( 20 );
   DECLARE LV_COUNT FLOAT;
   DECLARE A_MANDT     NVARCHAR(3) ARRAY;
   DECLARE A_MATBF     NVARCHAR(40) ARRAY;
   DECLARE A_WERKS     NVARCHAR(4) ARRAY;
   DECLARE A_CHARG_SID NVARCHAR(10) ARRAY;
   DECLARE A_BUDAT     NVARCHAR(8) ARRAY;
   DECLARE A_MAT_KDAUF NVARCHAR(10) ARRAY;
   DECLARE A_MAT_KDPOS NVARCHAR(6) ARRAY;
   DECLARE A_LGORT_SID NVARCHAR( 4) ARRAY;
   DECLARE A_quantity  DECIMAL ARRAY;
   DECLARE A_stock     DECIMAL ARRAY;
   DECLARE stock_quantity DECIMAL ARRAY;
   lt_stock = SELECT *    FROM zmmistockaging
           order by MATBF, WERKS, CHARG_SID, BUDAT DESC;
   a_MANDT    = ARRAY_AGG (:LT_Stock.MANDT ORDER BY MATBF, WERKS, CHARG_SID, BUDAT DESC);
   a_MATBF    = ARRAY_AGG (:LT_Stock.MATBF ORDER BY MATBF, WERKS, CHARG_SID, BUDAT DESC);
   a_WERKS    = ARRAY_AGG (:LT_Stock.WERKS ORDER BY MATBF, WERKS, CHARG_SID, BUDAT DESC);
   a_CHARG_SID = ARRAY_AGG (:LT_Stock.CHARG_SID ORDER BY MATBF, WERKS, CHARG_SID, BUDAT DESC);
   a_BUDAT    = ARRAY_AGG (:LT_Stock.BUDAT ORDER BY MATBF, WERKS, CHARG_SID, BUDAT DESC);
   a_quantity = ARRAY_agg (:lt_stock.stock_qty order by MATBF, WERKS, CHARG_SID, BUDAT DESC);
   

   for lv_index in 1 .. CARDINALITY(:a_mandt ) do
     a_stock[:lv_index] :=   a_quantity[:lv_index] + a_quantity[:lv_index + 1]; 
   end for;

   var_out = UNNEST(:a_mandt,:a_MATBF, :a_WERKS, :a_CHARG_SID, :a_BUDAT, :A_STOCK,:A_QUANTITY)
                 AS ("MANDT","MATBF","WERKS", "CHARG_SID", "BUDAT", "STOCK_QTY", "STOCK_BALANCE");
   return select * from :var_out;

 ENDMETHOD.
ENDCLASS.