11-26-2014 12:00 PM
Good morning all,
I've a problem with a query created with SQ02/SQ01.
I've selected just one table in my infoset, That table (called ZITW_TB_STORE_L2) has:
Number of pallet | number of coil | net weight | tare weight | gross weight
each pallet may have more coils on and so my table looks like:
900000000 800000000 3 KG 1 KG 4 KG
900000000 800000001 4 KG 1 KG 5 KG
900000000 800000002 2 KG 1 KG 3 KG
I wrote that code where:
lenum_fusto = Number of pallet
netto = net weight
tara = tare weight
lordo = gross weight
I need to sum all the weights of coils in one row for each pallet.
SELECT DISTINCT lenum_fusto INTO num_pallet
FROM zitw_tb_store_l2 INNER JOIN afpo
ON afpo~aufnr = zitw_tb_store_l2~aufnr
WHERE zitw_tb_store_l2~datum IN s_data
AND zitw_tb_store_l2~lenum_fusto IN s_pallet
AND afpo~pwerk IN s_werk.
CLEAR: v_netto, v_tara, v_lordo.
SELECT SUM( netto ) INTO v_netto
FROM zitw_tb_store_l2
WHERE lenum_fusto = num_pallet.
SELECT SUM( tara ) INTO v_tara
FROM zitw_tb_store_l2
WHERE lenum_fusto = num_pallet.
SELECT SUM( lordo ) INTO v_lordo
FROM zitw_tb_store_l2
WHERE lenum_fusto = num_pallet.
ENDSELECT.
I put this code on SE38 and the results is right (without number of coils):
Number of pallet | net weight | tare weight | gross weight
900000000 | 7KG | 3KG | 12KG
Unfortunatly, my code need to run in a query infoset, so i put it on "START-OF-SELECTION" section (i also tried in "Elaboration data") but as result I got three rows with sums like this:
Number of pallet | net weight | tare weight | gross weight
900000000 | 7KG | 3KG | 12KG
900000000 | 7KG | 3KG | 12KG
900000000 | 7KG | 3KG | 12KG
This because SAP does MY Select and ITS Select as shown below:
So it loops my result N time for each coils.
There is a way to avoid that SAP performs its selections?
11-27-2014 12:58 AM
I don't know why you NEED to use Querys, but if it's a must.
Why don't you feed your infoset directly with a Z report, it would be a lot easier than struggle with TABLES.
DATA: BEGIN OF gt_zitw_tb_store_l2 OCCURS 0,
lenum_fusto TYPE zitw_tb_store_l2-lenum_fusto,
netto TYPE zitw_tb_store_l2-netto,
tara TYPE zitw_tb_store_l2-tara,
lordo TYPE zitw_tb_store_l2-lordo,
END OF gt_zitw_tb_store_l2.
DATA: gt_final_table LIKE gt_zitw_tb_store_l2 OCCURS 0.
* Main SELECT
SELECT z~lenum_fusto z~netto z~tara z~lordo
FROM zitw_tb_store_l2 as z INNER JOIN afpo as a ON a~aufnr = z~aufnr
INTO TABLE gt_zitw_tb_store_l2
WHERE z~datum IN s_data
AND z~lenum_fusto IN s_pallet
AND a~pwerk IN s_werk.
* Now you have repeated lenum_fusto but you need to be only one entry per lenum_fusto
LOOP AT gt_zitw_tb_store_l2.
COLLECT gt_zitw_tb_store_l2 INTO gt_final_table.
ENDLOOP.
* Now you have in gt_final_table one row per lenum_fusto with all the numeric fields (netto, tara, lordo) sumarized.
* If you never used COLLECT check the SAP HELP
Regards
11-27-2014 12:58 AM
I don't know why you NEED to use Querys, but if it's a must.
Why don't you feed your infoset directly with a Z report, it would be a lot easier than struggle with TABLES.
DATA: BEGIN OF gt_zitw_tb_store_l2 OCCURS 0,
lenum_fusto TYPE zitw_tb_store_l2-lenum_fusto,
netto TYPE zitw_tb_store_l2-netto,
tara TYPE zitw_tb_store_l2-tara,
lordo TYPE zitw_tb_store_l2-lordo,
END OF gt_zitw_tb_store_l2.
DATA: gt_final_table LIKE gt_zitw_tb_store_l2 OCCURS 0.
* Main SELECT
SELECT z~lenum_fusto z~netto z~tara z~lordo
FROM zitw_tb_store_l2 as z INNER JOIN afpo as a ON a~aufnr = z~aufnr
INTO TABLE gt_zitw_tb_store_l2
WHERE z~datum IN s_data
AND z~lenum_fusto IN s_pallet
AND a~pwerk IN s_werk.
* Now you have repeated lenum_fusto but you need to be only one entry per lenum_fusto
LOOP AT gt_zitw_tb_store_l2.
COLLECT gt_zitw_tb_store_l2 INTO gt_final_table.
ENDLOOP.
* Now you have in gt_final_table one row per lenum_fusto with all the numeric fields (netto, tara, lordo) sumarized.
* If you never used COLLECT check the SAP HELP
Regards
11-27-2014 4:31 PM
My code already works and I'm sure that yours too.
I use query instead of abap report for some reasons and mainly because I need the "count" button on ALV grid and with abap report (in SE38) i don't know how to obtain it.
11-27-2014 5:01 PM
Hi.
Try to put some code in the END OF SELECTION of your infoset from SQ02 (in the code section).
Hope to help.
Bye
11-27-2014 7:09 PM
I dont know what you mean with "count" button, what it does? Im sure it can be done in tradicional ALV Report.
11-28-2014 8:36 AM
Hi Rodrigo,
I mean this button:
In SAP Query is shown by default but in standard report (generated manually from SE38) I don't know how to make it works.
11-29-2014 12:07 AM
The functionality exist in the menu.
Edit -> Calculate -> Count
If you find out grey out then you just need to specify a column to make the count, you could use an existing one or create a new one
gt_alv-count = '1'.
Then in the layout use this field (COUNTFNAME) new the new field COUNT or any other. You dont need to add the COUNT field to your catalog.
layout-countfname = 'COUNT'.
If you needed in a button just add one to your toolbar with this function code &COUNT
Regards
12-02-2014 1:08 PM
Hi, I solved my problem with your help and using this other thread:
In my include (declaration) I have this:
type-pools: slis.
types: ty_t_pdf type standard table of tline.
data: fieldcat type slis_t_fieldcat_alv,
v_repid like sy-repid.
data: layout type slis_layout_alv.
data: str_layout type slis_layout_alv,
str_print type slis_print_alv,
lv_buffer type string,
va_lines_txt type i,
va_lines_bin type i.
And before the function REUSE_ALV_GRID_DISPLAY i've done:
layout-countfname = 'ZCOUNT'
ZCOUNT is the last field on my internal table created in SE11 (INT1, lenght 3).