on 2017 Sep 11 1:10 PM
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
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
52 | |
10 | |
8 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.