09-11-2007 9:50 PM
Hello all,
I will try to lay this out as simply as possible. I have the following table in my code:
TYPES: BEGIN OF ty_table,
vbeln TYPE likp-vbeln,
lfdat TYPE likp-lfdat,
lfart TYPE likp-lfart,
wadat_ist TYPE likp-wadat_ist,
vstel TYPE likp-vstel,
route TYPE likp-route,
vsbed TYPE likp-vsbed,
inco1 TYPE likp-inco1,
inco2 TYPE likp-inco2,
kunnr TYPE likp-kunnr,
kunag TYPE likp-kunag,
brgew TYPE lips-brgew,
matnr TYPE lips-matnr,
arktx TYPE lips-arktx,
meins TYPE lips-meins,
lgmng TYPE lips-lgmng,
vgbel TYPE lips-vgbel,
name2 TYPE adrc-name2,
street TYPE adrc-street,
city1 TYPE adrc-city1,
region TYPE adrc-region,
post_code1 TYPE adrc-post_code1,
lifnr TYPE lfa1-lifnr,
name1 TYPE adrc-name1,
so_vbeln TYPE vbak-vbeln,
erdat TYPE vbak-erdat,
END OF ty_table.
DATA: it_table TYPE TABLE OF ty_table.
I then run through a select statement as follows:
* Get data from LIKP and LIPS.
SELECT a~vbeln a~lfdat a~lfart a~wadat_ist a~vstel a~route a~vsbed b~brgew a~inco1 a~inco2 a~kunnr a~kunag
b~matnr b~meins b~lgmng b~vgbel b~arktx INTO CORRESPONDING FIELDS OF TABLE it_table
FROM likp AS a
INNER JOIN lips AS b ON b~vbeln = a~vbeln
WHERE a~vstel IN so_vstel
AND a~wadat_ist IN so_wadat
AND b~mtart IN so_mtart.
That works fine. The next statement, in the report, is the following select. When this next select runs it clears all of the fields except for vbeln and erdat. I want c~vbeln to go into it_table-so_vbeln. Now it is going into it_table-vbeln.
* Get Sales order info from VBAK
SELECT c~vbeln c~erdat INTO CORRESPONDING FIELDS OF TABLE it_table
FROM lips AS a
INNER JOIN vbap AS b ON b~vbeln = a~vgbel
AND b~posnr = a~vgpos
INNER JOIN vbak AS c ON c~vbeln = b~vbeln
FOR ALL ENTRIES IN it_table
WHERE a~vgbel = it_table-vgbel.
I then tried to write the select statement by takign out "into corresponding fields" like the following but it gives my a syntax error on (it_table-so_vbeln , it_table-erdat).
* Get Sales order info from VBAK
SELECT c~vbeln c~erdat INTO (it_table-so_vbeln , it_table-erdat)
FROM lips AS a
INNER JOIN vbap AS b ON b~vbeln = a~vgbel
AND b~posnr = a~vgpos
INNER JOIN vbak AS c ON c~vbeln = b~vbeln
FOR ALL ENTRIES IN it_table
WHERE a~vgbel = it_table-vgbel.
Is there a way to write the second select (the select that is pulling data from VBAK) so that it will not clear the other entries in the table? Also, is there a way to combine the two selects into one join?
Regards,
Davis
09-11-2007 11:15 PM
Hi,
Sorry ... please try this (cut and paste below codes).
TYPES: BEGIN OF ty_table,
vbeln TYPE likp-vbeln,
lfdat TYPE likp-lfdat,
lfart TYPE likp-lfart,
wadat_ist TYPE likp-wadat_ist,
vstel TYPE likp-vstel,
route TYPE likp-route,
vsbed TYPE likp-vsbed,
inco1 TYPE likp-inco1,
inco2 TYPE likp-inco2,
kunnr TYPE likp-kunnr,
kunag TYPE likp-kunag,
brgew TYPE lips-brgew,
matnr TYPE lips-matnr,
arktx TYPE lips-arktx,
meins TYPE lips-meins,
lgmng TYPE lips-lgmng,
vgbel TYPE lips-vgbel,
so_vbeln TYPE vbak-vbeln, "Change here
erdat TYPE vbak-erdat, "Change here
name2 TYPE adrc-name2,
street TYPE adrc-street,
city1 TYPE adrc-city1,
region TYPE adrc-region,
post_code1 TYPE adrc-post_code1,
lifnr TYPE lfa1-lifnr,
name1 TYPE adrc-name1,
END OF ty_table.
DATA: it_table TYPE TABLE OF ty_table.
SELECT a~vbeln a~lfdat a~lfart a~wadat_ist a~vstel a~route a~vsbed a~inco1 a~inco2 a~kunnr a~kunag
b~brgew b~matnr b~arktx b~meins b~lgmng b~vgbel
d~vbeln d~erdat " Change here
INTO TABLE it_table
FROM likp AS a
INNER JOIN lips AS b ON b~vbeln = a~vbeln
INNER JOIN vbap AS c ON c~vbeln = b~vgbel
AND b~posnr = b~vgpos
INNER JOIN vbak AS d ON d~vbeln = c~vbeln
WHERE a~vstel IN so_vstel
AND a~wadat_ist IN so_wadat
AND b~mtart IN so_mtart.
Regards,
Ferry Lianto
09-11-2007 9:53 PM
Following is the entire report, if that makes it easier:
*&---------------------------------------------------------------------*
*& Report Z_TRANS_EVAL
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT z_trans_eval.
TABLES: likp, lips, adrc, vbpa.
TYPE-POOLS: truxs.
TYPES: BEGIN OF ty_table,
vbeln TYPE likp-vbeln,
lfdat TYPE likp-lfdat,
lfart TYPE likp-lfart,
wadat_ist TYPE likp-wadat_ist,
vstel TYPE likp-vstel,
route TYPE likp-route,
vsbed TYPE likp-vsbed,
inco1 TYPE likp-inco1,
inco2 TYPE likp-inco2,
kunnr TYPE likp-kunnr,
kunag TYPE likp-kunag,
brgew TYPE lips-brgew,
matnr TYPE lips-matnr,
arktx TYPE lips-arktx,
meins TYPE lips-meins,
lgmng TYPE lips-lgmng,
vgbel TYPE lips-vgbel,
name2 TYPE adrc-name2,
street TYPE adrc-street,
city1 TYPE adrc-city1,
region TYPE adrc-region,
post_code1 TYPE adrc-post_code1,
lifnr TYPE lfa1-lifnr,
name1 TYPE adrc-name1,
so_vbeln TYPE vbak-vbeln,
erdat TYPE vbak-erdat,
END OF ty_table.
DATA: it_table TYPE TABLE OF ty_table,
wa_table LIKE LINE OF it_table,
it_table_csv TYPE truxs_t_text_data.
DATA : lv_tabix LIKE sy-tabix.
DATA temp_string TYPE string.
DATA w_adrnr TYPE vbpa-adrnr.
DATA: w_adrnr_sp TYPE vbpa-adrnr.
* Selction criteria
SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-004.
SELECT-OPTIONS: so_vstel FOR likp-vstel,
so_wadat FOR likp-wadat_ist,
so_mtart FOR lips-mtart.
SELECTION-SCREEN END OF BLOCK b2.
* CSV output filename
SELECTION-SCREEN BEGIN OF BLOCK b3 WITH FRAME TITLE text-003.
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (16) text-002 FOR FIELD p_file.
PARAMETERS: p_file(30) TYPE c.
SELECTION-SCREEN END OF LINE.
SELECTION-SCREEN END OF BLOCK b3.
START-OF-SELECTION.
* Get data from LIKP and LIPS.
SELECT a~vbeln a~lfdat a~lfart a~wadat_ist a~vstel a~route a~vsbed b~brgew a~inco1 a~inco2 a~kunnr a~kunag
b~matnr b~meins b~lgmng b~vgbel b~arktx INTO CORRESPONDING FIELDS OF TABLE it_table
FROM likp AS a
INNER JOIN lips AS b ON b~vbeln = a~vbeln
WHERE a~vstel IN so_vstel
AND a~wadat_ist IN so_wadat
AND b~mtart IN so_mtart.
* Get Sales order info from VBAK
SELECT c~vbeln c~erdat INTO CORRESPONDING FIELDS OF TABLE it_table
FROM lips AS a
INNER JOIN vbap AS b ON b~vbeln = a~vgbel
AND b~posnr = a~vgpos
INNER JOIN vbak AS c ON c~vbeln = b~vbeln
FOR ALL ENTRIES IN it_table
WHERE a~vgbel = it_table-vgbel.
* Get internal address number for ship to party
LOOP AT it_table INTO wa_table.
lv_tabix = sy-tabix. "update counter.
SELECT SINGLE adrnr FROM vbpa INTO w_adrnr WHERE
vbeln = wa_table-vgbel
AND parvw = 'WE'.
SELECT SINGLE lifnr FROM vbpa INTO wa_table-lifnr WHERE
vbeln = wa_table-vbeln
AND parvw = 'SP'
AND posnr = '000000'.
SELECT SINGLE name1 FROM lfa1 INTO wa_table-name1 WHERE
lifnr = wa_table-lifnr.
* Get address data from VBPA
SELECT city1 region post_code1 INTO CORRESPONDING FIELDS OF wa_table
FROM adrc WHERE
addrnumber = w_adrnr.
ENDSELECT.
* Update the internal table
MODIFY it_table INDEX lv_tabix FROM wa_table.
ENDLOOP.
END-OF-SELECTION.
* Call correct display procedure(s).
IF p_file <> ''.
* Save a CSV file
PERFORM display_csv.
ELSE.
*Display in ALV
PERFORM display_alv.
ENDIF.
*&---------------------------------------------------------------------*
*& Form display_alv
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM display_alv .
DATA: gr_alv TYPE REF TO cl_salv_table,
gr_func TYPE REF TO cl_salv_functions,
gr_columns TYPE REF TO cl_salv_columns_table,
gr_column TYPE REF TO cl_salv_column_table,
gr_error TYPE REF TO cx_salv_error.
TRY.
CALL METHOD cl_salv_table=>factory
IMPORTING
r_salv_table = gr_alv
CHANGING
t_table = it_table.
CATCH cx_salv_msg INTO gr_error.
ENDTRY.
gr_func = gr_alv->get_functions( ).
gr_func->set_all( abap_true ).
gr_alv->display( ).
ENDFORM. " display_alv
*&---------------------------------------------------------------------*
*& Form display_csv
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM display_csv .
DATA: w_filename TYPE string.
CONCATENATE p_file temp_string INTO w_filename.
CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'
EXPORTING
i_field_seperator = ';'
TABLES
i_tab_sap_data = it_table
CHANGING
i_tab_converted_data = it_table_csv
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = w_filename
TABLES
data_tab = it_table_csv
EXCEPTIONS
OTHERS = 1.
PERFORM display_alv.
ENDFORM. " display_csv
09-11-2007 10:01 PM
I don't think it's good practice to SELECT into the same table you are using in the FOR ALL ENTRIES. Try using a separate table and then combining them later.
Rob
09-11-2007 10:10 PM
Rob,
I did not take that into consideration but what you said makes sense. I will have to do some experimentation to see the performance of such statements. Thanks you for bringing that to my attention!
Davis
09-11-2007 10:02 PM
Hi,
Please try this.
SELECT a~vbeln a~lfdat a~lfart a~wadat_ist a~vstel a~route a~vsbed b~brgew a~inco1 a~inco2 a~kunnr a~kunag b~matnr b~meins b~lgmng b~vgbel b~arktx
d~vbeln d~erdat
INTO CORRESPONDING FIELDS OF TABLE it_table
FROM likp AS a
INNER JOIN lips AS b ON b~vbeln = a~vbeln
INNER JOIN vbap AS c ON c~vbeln = b~vgbel
AND b~posnr = b~vgpos
INNER JOIN vbak AS d ON d~vbeln = c~vbeln
WHERE a~vstel IN so_vstel
AND a~wadat_ist IN so_wadat
AND b~mtart IN so_mtart.
Regards,
Ferry Lianto
09-11-2007 10:08 PM
Ferry,
That works great except that it stores dvbeln into it_table-vbeln when it should store dvbeln in it_table-so_vbeln. That is where I am stuck. I do not know how to get it into it_table-so_vbeln without doing the select inside a loop and using the work area.
Regards,
Davis
09-11-2007 10:13 PM
Hi,
Because of the field name selected (VBAK-VBELN) is same as IT_TABLE-VBELN.
Please try to rename IT_TABLE-VBELN to IT_TABLE-VBELN1 and IT_TABLE-SO_VBELN to IT_TABLE-VBELN. The values VBAK-VBELN should append to IT_TABLE-VBELN.
Regards,
Ferry Lianto
09-11-2007 10:27 PM
Ferry,
It looks to me like I can not do this in one select statement. If I rename it_table-vbeln to it_table-vbeln1 then vbeln1 will not get populated. Am I correct in assuming that this can't be done in one select statement? Or can it if I use
select ... into (f1 , f2 , f3 , fn).
I have never tried specifying the target fields inside a join. I am not sure that it will work being that I am selecting into an internal table though. I will have to try that tomorrow morning.
Thanks for your help. I will leave this opening until tomorrow.
Davis
09-11-2007 11:15 PM
Hi,
Sorry ... please try this (cut and paste below codes).
TYPES: BEGIN OF ty_table,
vbeln TYPE likp-vbeln,
lfdat TYPE likp-lfdat,
lfart TYPE likp-lfart,
wadat_ist TYPE likp-wadat_ist,
vstel TYPE likp-vstel,
route TYPE likp-route,
vsbed TYPE likp-vsbed,
inco1 TYPE likp-inco1,
inco2 TYPE likp-inco2,
kunnr TYPE likp-kunnr,
kunag TYPE likp-kunag,
brgew TYPE lips-brgew,
matnr TYPE lips-matnr,
arktx TYPE lips-arktx,
meins TYPE lips-meins,
lgmng TYPE lips-lgmng,
vgbel TYPE lips-vgbel,
so_vbeln TYPE vbak-vbeln, "Change here
erdat TYPE vbak-erdat, "Change here
name2 TYPE adrc-name2,
street TYPE adrc-street,
city1 TYPE adrc-city1,
region TYPE adrc-region,
post_code1 TYPE adrc-post_code1,
lifnr TYPE lfa1-lifnr,
name1 TYPE adrc-name1,
END OF ty_table.
DATA: it_table TYPE TABLE OF ty_table.
SELECT a~vbeln a~lfdat a~lfart a~wadat_ist a~vstel a~route a~vsbed a~inco1 a~inco2 a~kunnr a~kunag
b~brgew b~matnr b~arktx b~meins b~lgmng b~vgbel
d~vbeln d~erdat " Change here
INTO TABLE it_table
FROM likp AS a
INNER JOIN lips AS b ON b~vbeln = a~vbeln
INNER JOIN vbap AS c ON c~vbeln = b~vgbel
AND b~posnr = b~vgpos
INNER JOIN vbak AS d ON d~vbeln = c~vbeln
WHERE a~vstel IN so_vstel
AND a~wadat_ist IN so_wadat
AND b~mtart IN so_mtart.
Regards,
Ferry Lianto
09-12-2007 12:07 AM
Ferry,
Thanks for that last post! I don't know why I didn't think of taking the "into corresponding fields" part. I see no reason for it not to work, as you posted. I will leave this open until I have a chance to test it.
Thanks again!
Davis