topic Re: Quick help with a select statement... in Application Development and Automation Discussions

Quick help with a select statement...

Former Member — Tue, 11 Sep 2007 20:50:42 GMT

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

Re: Quick help with a select statement...

Former Member — Tue, 11 Sep 2007 20:53:10 GMT

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

Re: Quick help with a select statement...

Former Member — Tue, 11 Sep 2007 21:01:59 GMT

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

Re: Quick help with a select statement...

ferry_lianto — Tue, 11 Sep 2007 21:02:06 GMT

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

Re: Quick help with a select statement...

Former Member — Tue, 11 Sep 2007 21:08:15 GMT

Ferry,

That works great except that it stores d_{vbeln into it_table-vbeln when it should store d}vbeln 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

Re: Quick help with a select statement...

Former Member — Tue, 11 Sep 2007 21:10:50 GMT

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

Re: Quick help with a select statement...

ferry_lianto — Tue, 11 Sep 2007 21:13:24 GMT

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

Re: Quick help with a select statement...

Former Member — Tue, 11 Sep 2007 21:27:42 GMT

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

Re: Quick help with a select statement...

ferry_lianto — Tue, 11 Sep 2007 22:15:37 GMT

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

Re: Quick help with a select statement...

Former Member — Tue, 11 Sep 2007 23:07:11 GMT

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