All fields are needed during INNER JOIN

[Former Member]

Dear all,

I come across with a scenario during the inner join of table A and table B, the result should contains all the fields from table A and B. Normally we will only select particular fields that we need in the INNER JOIN statement, but never have a chance to select all fields like this.

Hence, i would need your advice on <u>how to code</u> by selecting all fields from table A and B during INNER JOIN.

Thanks in advance.

[Former Member]

Dear all,

I come across with a scenario during the inner join of table A and table B, the result should contains all the fields from table A and B. Normally we will only select particular fields that we need in the INNER JOIN statement, but never have a chance to select all fields like this.

Hence, i would need your advice on <u>how to code</u> by selecting all fields from table A and B during INNER JOIN.

Thanks in advance.

[Former Member]

Hi,

Check this Sample code,

tables: zvijirank,
        zvijirank1.
*        ZVIJIRANK2.

data : a type zvijirank occurs 10 with header line,
       b type zvijirank1 occurs 10 with header line.
*       C TYPE ZVIJIRANK2 OCCURS 10 WITH HEADER LINE.

data : name like zvijirank-name,
       total like zvijirank1-total,
       reg_no like zvijirank.
*       regno type i.


data : begin of wa,
       name type zvijirank-name,
       total type zvijirank1-total,
       branch type zvijirank1-branch,
       reg_no type zvijirank-reg_no,
*       FNAME TYPE ZVIJIRANK2-F_NAME,
*       MNAME TYPE ZVIJIRANK2-M_NAME,
*       CITY TYPE ZVIJIRANK2-CITY,
*       TEL_NO TYPE ZVIJIRANK2-TEL_NO,
       end of wa.



data : it like standard table of wa with header line,
       it1 like standard table of wa with header line.

*
selection-screen : begin of screen 9010.
parameters : regno type i matchcode object zreg.
selection-screen : end of screen 9010.

*regno = '00100'.

call selection-screen 9010.

select a~name b~total b~branch a~reg_no into table it from zvijirank as a inner join zvijirank1 as b on a~reg_no = b~reg_no .

loop at it.

if it-reg_no eq regno.

write : 'NAME   :', it-name,
        'REG_NO :', it-reg_no,
         'TOTAL :', it-total.

ENDIF.
endloop.

Thanks,

Reward If Helpful.

[Former Member]

check this sample code

tables : mara,mard.

data : begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

werks like mard-werks,

lgort like mard-lgort,

end of itab.

select * <b>into corresponding fields of table itab</b> from mara as a join

mard as b on a_{matnr = b}matnr up to 30 rows.

loop at itab.

write : / itab-matnr, itab-mtart,itab-werks,itab-lgort.

endloop.

regards

shiba dutta

[Former Member]

Hi shiba dutta,

Thanks for your prompt reply. However to be specific below is my scenario:

I need to perform inner join between MARA and MARC. Where the result only need to grab all fields from MARC. And im using code below:

TYPES: BEGIN OF TY_MARC.
           INCLUDE STRUCTURE MARC.
TYPES: END OF TY_MARC.
DATA: TA_MARC TYPE STANDARD TABLE OF TY_MARC.

SELECT * INTO CORRESPONDING FIELD OF TABLE TA_MARC
  FROM MARA AS A INNER JOIN MARC AS C
  ON A~MATNR = C~MATNR
  WHERE A~MATNR IN SO_MATNR. 

Since MARC hacing 200++ fields, im just worried for the statement "INTO CORRESPONDING" will dramatically reduce the selection performance. What you think? Is there a better way to achieve the above?

Please comment.

[Former Member]

HI FSCHU,

what is the need of into corresponding fields here beause u have already taken the entire structure of marc

i think it is not needed

regards

sandhya

[Former Member]

yes it is correct that into corresponding fields will decrese the performance. But i think there is no other way.

Why dont you try for all entries?

try this sample code.

tables : mara, marc.

data : imarc like table of marc with header line.

data : imara like table of mara with header line.

select * from mara into table imara up to 10 rows.

if not imara[] is initial.

select * from marc into table imarc for all entries in imara where matnr = imara-matnr.

endif.

regards

shiba dutta

[Former Member]

Hi shiba dutta,

Basically i did tried on the FOR ALL ENTRIES as well. <u>But the result doesn't seems better than the INNER JOIN</u>, because the number of data being processed is <b>100,000</b> where it caused FOR ALL ENTRIES performing bad.

I did posted a thread for this issue with subject "issue FOR ALL ENTRIES".

Could you please comment.

[Former Member]

Hi,

there's a nice workaround - you can of course select all fields of just one table in the SELECT command with INNER JOIN - first prepare the list of fields (all fields of a table) to be selected - AUTOMATICALLY - and then perform the desired SELECT command

Fell free to see and use the code snippet from my blog: ABAP - Select * (all columns) and INNER JOIN