Inner join with SELECT *

[dharma_esampalli]

Hi all,

Can I write my INNER JOIN SELECT statement with SELECT * in ABAP.

IF POSSIBLE PLEASE LET ME KNOW.

Actually I write Inner join in this way,

SELECT EKKO~EBELN EKKO~BSART

               EKPO~MATNR EKPO~WERKS

               INTO TABLE gt_ekko_ekpo

               FROM ekko INNER JOIN ekpo ON ekko~ebeln = ekpo~ebeln

               FOR ALL ENTRIES IN gt_ekbe.

But if I want all the fields of EKKO and EKPO, its little difficult to write ALL FIELDS. So any method of writing such statements.

Thanks & regards,

Sekhar

[matt]

It is unusual to actually need all fields. Do you really really need them, are you really using all the fields? Or is it lazy table definition? If only the fields you are using are specified, this is usually better and safer programming.

You can reduce the amount of typing with aliases, which also makes the code more readable. So:

SELECT k~EBELN k~BSART

               k~MATNR p~WERKS

               INTO TABLE gt_ekko_ekpo

               FROM ekko as k INNER JOIN ekpo as p ON k~ebeln = p~ebeln.

Have you tried using SELECT * ? What did you get?

[custodio_deoliveira]

Matthew Billingham wrote:
It is unusual to actually need all fields. Do you really really need them, are you really using all the fields? Or is it lazy table definition? If only the fields you are using are specified, this is usually better and safer programming.
You can reduce the amount of typing with aliases, which also makes the code more readable. So:
SELECT k~EBELN k~BSART
               k~MATNR p~WERKS
               INTO TABLE gt_ekko_ekpo
               FROM ekko as k INNER JOIN ekpo as p ON k~ebeln = p~ebeln.
Have you tried using SELECT * ? What did you get?

I am totally for the aliases and agree with you, but was recently criticized by the "Clarity Police" for using them because (guess what?) they would make code less readable!!!

My lesson from this is: readability lies in the eyes of the reader.

Cheers,

Custodio

[venkateswaran_k]

Hi

Define your  gt_ekko_ekpo Internal table structure with all your required fields.

in your select statement - write as follows

SELECT *              

               FROM ekko AS EKKO INNER JOIN ekpo AS EKPO ON ekko~ebeln = ekpo~ebeln

               INTO CORRESPONDING FIELDS OF  TABLE gt_ekko_ekpo

               FOR ALL ENTRIES IN gt_ekbe.

Regards,

Venkat

[Former Member]

Hi,

Refer Thomas's answer here, http://scn.sap.com/thread/1117125

BR,

Ankit.

[pathum_madhuranga]

Hi Sekhar,

You don't need to write all the fields, instead of that you can put  star mark (*)  then it will takes all the fields from tables involve in inner join.

Thanx,

Pathum.

[Former Member]

This message was moderated.

[Clemenss]

Hi Sekhar Esampalli,

OK, if you want all fields there is a way:

As you have some fieldnames equal in both tables, you can not use * because the database cannot from which table the field shall come.

You may define a table of fieldnames to use in the select statement.

Step 1: define your target structure with unique fieldnames.

Step 2: get fields of this structure in internal table (Form  GETFIELDS)

Step 3: put <tabname>~ of source table in front of fieldname for all joined tables (Form FIELDS_ADD_JOIN_TABLE)

Step 4: modify field name table with special alias fields if required

Step 5: Execute select

Here a sample:

FORM data_select CHANGING ct_outrec TYPE ty_t_outrec.
  DATA:
    lt_field                TYPE TABLE OF string.
  PERFORM getfields
    USING    ct_outrec
    CHANGING lt_field.
  PERFORM data_select_adapt_fields CHANGING lt_field.
  PERFORM fields_add_join_table USING:
    'VBAP'     CHANGING lt_field,
    'VBAK'     CHANGING lt_field,
    'KNA1'     CHANGING lt_field,
    'MAKT'     CHANGING lt_field.
  DELETE lt_field WHERE NOT table_line CA '~'.
  SELECT (lt_field)
    INTO CORRESPONDING FIELDS OF TABLE ct_outrec
    FROM vbap
    JOIN vbak ON vbak~vbeln = vbap~vbeln
    JOIN kna1 ON vbak~kunnr = kna1~kunnr
    JOIN makt ON makt~matnr = vbap~matnr
             AND makt~spras = kna1~spras
WHERE auart  
= p_auart
  
AND vkorg  
= p_vkorg
  
AND vtweg  
IN s_vtweg
  
AND vbak~spart IN s_spart
  
AND vbak~vbeln IN s_vbeln
  
AND vbak~kunnr IN s_kunnr
  
AND vbak~erdat IN s_erdat.

OK, here the service forms:

*&---------------------------------------------------------------------*
*&      Form  FIELDS_ADD_JOIN_TABLE
*&---------------------------------------------------------------------*
*       Put Join table name before Feldname for dynamic select
*       i.e. MATNR -> MARA~MATNR
*----------------------------------------------------------------------*
*      -->PV_TABNAME
*      <--PT_FIELD
*----------------------------------------------------------------------*
FORM fields_add_join_table  USING    pv_tabname TYPE tabname
                            CHANGING pt_field   TYPE table.
  DATA:
    lt_fieldname          TYPE SORTED TABLE OF fieldname
      WITH UNIQUE KEY table_line,
    lv_fieldname          TYPE fieldname  .
  FIELD-SYMBOLS:
    <fieldname>           TYPE fieldname,
    <any>                 TYPE ANY.
  CHECK pt_field IS NOT INITIAL.
  LOOP AT pt_field ASSIGNING <any>.
    CHECK <any> NA '~'.
    lv_fieldname = <any>.
    INSERT lv_fieldname INTO TABLE lt_fieldname.
  ENDLOOP.
  CHECK lt_fieldname IS NOT INITIAL.
  SELECT fieldname
    INTO TABLE lt_fieldname
    FROM dd03l
    FOR ALL ENTRIES IN lt_fieldname
    WHERE tabname = pv_tabname
      AND fieldname = lt_fieldname-table_line.
  LOOP AT pt_field ASSIGNING <any>.
    CHECK <any> NA '~'.
    lv_fieldname = <any>.
    READ TABLE lt_fieldname ASSIGNING <fieldname>
      WITH TABLE KEY table_line = lv_fieldname.
    CHECK sy-subrc = 0.
    CONCATENATE pv_tabname '~' <any> INTO <any>.
  ENDLOOP.
ENDFORM.                    " FIELDS_ADD_JOIN_TABLE

*&---------------------------------------------------------------------*
*&      Form  GETFIELDS
*&---------------------------------------------------------------------*
*       get component field names for any table or structure
*----------------------------------------------------------------------*
*      -->PX_DATA
*      <--PT_FIELD
*----------------------------------------------------------------------*
FORM getfields
  USING    px_data              TYPE any
  CHANGING pt_field            TYPE table.
  DATA:
    lt_comp                     TYPE abap_compdescr_tab,
    lr_dat                      TYPE REF TO data,
    lr_typedescr                TYPE REF TO cl_abap_typedescr,
    lr_structdescr              TYPE REF TO cl_abap_structdescr.
  FIELD-SYMBOLS:
    <fs>                        TYPE ANY,
    <ft>                        TYPE ANY TABLE,
    <comp>                      TYPE LINE OF abap_compdescr_tab.
  lr_typedescr = cl_abap_typedescr=>describe_by_data( px_data ).
  CASE lr_typedescr->kind.
    WHEN 'S'.
      lr_structdescr ?= lr_typedescr.
      lt_comp                             = lr_structdescr->components.
    WHEN 'T'.
      ASSIGN px_data TO <ft>.
      CREATE DATA lr_dat                  LIKE LINE OF <ft>.
      ASSIGN lr_dat->* TO <fs>.
      lr_structdescr ?= cl_abap_structdescr=>describe_by_data( <fs> ).
      lt_comp                             = lr_structdescr->components.
    WHEN OTHERS.
      MESSAGE e241(00).
*   Function is invalid in this environment
  ENDCASE.
  CLEAR pt_field.
  LOOP AT lt_comp ASSIGNING <comp>.
    APPEND <comp>-name TO pt_field.
  ENDLOOP." at lt_comp assigning <comp>.
ENDFORM.                    "getfields

*&---------------------------------------------------------------------*
*&      Form  DATA_SELECT_ADAPT_FIELDS
*&---------------------------------------------------------------------*
*       Add alias for joined selection of ambiguous fields
*----------------------------------------------------------------------*
*      <--P_LT_FIELD  text
*----------------------------------------------------------------------*
FORM data_select_adapt_fields  CHANGING pt_field TYPE table.
  FIELD-SYMBOLS:
    <field>             TYPE ANY.
  LOOP AT pt_field ASSIGNING <field>.
    CASE <field>.
      WHEN 'KUNWE'.
        CONCATENATE 'VBPA~KUNNR AS' <field> INTO  <field>
          SEPARATED BY space.
      WHEN 'NAME1_WE'.
        CONCATENATE 'KNA1_WE~NAME1 AS' <field> INTO  <field>
          SEPARATED BY space.
      WHEN 'LAND1_WE'.
        CONCATENATE 'KNA1_WE~LAND1 AS' <field> INTO  <field>
          SEPARATED BY space.
ENDFORM.                    " DATA_SELECT_ADAPT_FIELDS

Please adapt to your needs.

Regards

Clemens

[Former Member]

Hi Sekhar,

You can use select * from ekpo into gt_ekpo with for all entries in gt_ekbe.

Then you can fetch the ekko details using for all entries in gt_ekpo.

Performance wise it is nice when compared with joins.

Later on you can use parallel cursor if necessary.

[ThomasZloch]

If you really need all fields from both tables, here is a rather simple way that I came across just recently, which also eliminates the issue with duplicate field names:

TYPES: BEGIN OF ty_ekko_ekpo,
         ekko TYPE ekko,
         ekpo TYPE ekpo,
       END OF ty_ekko_ekpo.
DATA: gt_ekko_ekpo TYPE STANDARD TABLE OF ty_ekko_ekpo.

SELECT * INTO TABLE gt_ekko_ekpo
       FROM ekko JOIN ekpo ON ekko~ebeln = ekpo~ebeln

etc...

It is mandatory to maintain the same table sequence in the data declaration and in the select join statement.

Thomas