topic Re: Inner join with SELECT * in Application Development and Automation Discussions

Inner join with SELECT *

dharma_esampalli — Sat, 01 Jun 2013 07:29:11 GMT

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

Re: Inner join with SELECT *

matt — Sat, 01 Jun 2013 14:55:15 GMT

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?

Re: Inner join with SELECT *

venkateswaran_k — Sat, 01 Jun 2013 14:56:29 GMT

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

Re: Inner join with SELECT *

Former Member — Sat, 01 Jun 2013 17:12:17 GMT

Hi,

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

BR,

Ankit.

Re: Inner join with SELECT *

pathum_madhuranga — Sun, 02 Jun 2013 14:48:51 GMT

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.

Re: Inner join with SELECT *

Former Member — Sun, 02 Jun 2013 16:52:11 GMT

This message was moderated.

Re: Inner join with SELECT *

Clemenss — Sun, 02 Jun 2013 17:55:30 GMT

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

Re: Inner join with SELECT *

custodio_deoliveira — Mon, 03 Jun 2013 04:20:27 GMT

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

Re: Inner join with SELECT *

Former Member — Tue, 04 Jun 2013 10:02:28 GMT

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.

Re: Inner join with SELECT *

ThomasZloch — Fri, 07 Jun 2013 10:45:37 GMT

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

	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.