Skip to Content

Quick help with a select statement...

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    Posted on Sep 11, 2007 at 10: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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Sep 11, 2007 at 08: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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 11, 2007 at 09: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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 11, 2007 at 09: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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Sep 11, 2007 at 09: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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.