topic Re: All fields are needed during INNER JOIN in Application Development and Automation Discussions

All fields are needed during INNER JOIN

Former Member — Sat, 13 Oct 2007 06:30:05 GMT

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 how to code by selecting all fields from table A and B during INNER JOIN.

Thanks in advance.

Re: All fields are needed during INNER JOIN

Former Member — Sat, 13 Oct 2007 06:34:02 GMT

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.

Re: All fields are needed during INNER JOIN

Former Member — Sat, 13 Oct 2007 06:41:45 GMT

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 * into corresponding fields of table itab 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

Re: All fields are needed during INNER JOIN

Former Member — Sat, 13 Oct 2007 07:25:32 GMT

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.

Re: All fields are needed during INNER JOIN

Former Member — Sat, 13 Oct 2007 08:00:15 GMT

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

Re: All fields are needed during INNER JOIN

Former Member — Sat, 13 Oct 2007 08:09:03 GMT

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

Re: All fields are needed during INNER JOIN

Former Member — Sat, 13 Oct 2007 08:17:21 GMT

Hi shiba dutta,

Basically i did tried on the FOR ALL ENTRIES as well. But the result doesn't seems better than the INNER JOIN, because the number of data being processed is 100,000 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.

Re: All fields are needed during INNER JOIN

Former Member — Tue, 31 Mar 2015 08:21:27 GMT

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