topic Re: join tables in Application Development and Automation Discussions

join tables

Former Member — Thu, 13 Mar 2008 07:44:25 GMT

how to join 3-4 tables with good performance with coding

reply me soon.......

Re: join tables

Former Member — Thu, 13 Mar 2008 07:48:30 GMT

You can do it in two ways:

1. Use inner joins to select the data from each of these tables together. But you need to be careful and sure about he joining conditions.

2. Select serially from one table and use for all entries in the next select statement.

This will also work.

- Guru

Reward points if useful

Re: join tables

Former Member — Thu, 13 Mar 2008 07:48:57 GMT

Joining 3 tables is not suggested by SAP.

First try to fetch all the Master entries into an Internal table.

Use a Join for the remaining 2 tables with a FOR ALL ENTRIES condition on the Master Internal table.

Don't forget to put sy-subrc checks, DELETE Duplicates..., SORT, READ, etc. before for all Entries.

Hope this helps

Regards

Vinayak

Re: join tables

Former Member — Thu, 13 Mar 2008 07:50:35 GMT

hi,

by apply inner join condition.

select i_{recdv i}cdiff i_{itime i}idate i_{vlcod i}point i_{mdtxt i}mdocm m_{objnr m}swerk e_{eqktx t}atinn e~equnr into CORRESPONDING FIELDS OF table it

from imrg as i

inner join imptt as t on i_{point = t}point

inner join mcipmis as m on m_{objnr = t}mpobj

inner join eqkt as e on e_{equnr = m}equnr

where m~swerk in swerk

and i~idate in idate

and t~atinn in ('0000000058' , '0000000151' , '0000000152')

and i~cancl <> 'X'.

use this type of condition in your program

reward point if helpful.

regards,

pankaj

Re: join tables

ak_upadhyay — Thu, 13 Mar 2008 07:53:03 GMT

Hi Ashwani,

Check this report....


*&---------------------------------------------------------------------*
*& Report  ZCUSTOMER_DETAIL                                            *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&                                                                     *
*&---------------------------------------------------------------------*

REPORT  ZCUSTOMER_DETAIL                        .

TABLES : KNA1, VBAK, VBAP, LIPS, VBRP, VBRK, KNKK.

PARAMETERS : KUNNR LIKE KNA1-KUNNR.

DATA : BEGIN OF IT OCCURS 0,
       KUNNR LIKE KNA1-KUNNR,
       NAME1 LIKE KNA1-NAME1,
       FKDAT LIKE VBRK-FKDAT,
       KLIMK LIKE KNKK-KLIMK,
       KKBER LIKE KNKK-KKBER,
       END OF IT.

    SELECT SINGLE
                KNA1~KUNNR
                KNA1~NAME1
                VBRK~FKDAT
                KNKK~KLIMK
                KNKK~KKBER
                INTO IT FROM KNA1 INNER JOIN VBAK ON
                KNA1~KUNNR = VBAK~KUNNR
                INNER JOIN KNKK ON
                KNA1~KUNNR = KNKK~KUNNR
                INNER JOIN VBAP ON
                VBAK~VBELN = VBAP~VBELN
                INNER JOIN LIPS ON
                VBAP~VBELN = LIPS~VGBEL
                INNER JOIN VBRP ON
                LIPS~VBELN = VBRP~VGBEL
                INNER JOIN VBRK ON
                VBRP~VBELN = VBRK~VBELN
                WHERE KNA1~KUNNR = KUNNR.

    APPEND IT.

   LOOP AT IT.

   WRITE :/ IT-KUNNR,
            IT-NAME1,
            IT-FKDAT,
            IT-KLIMK,
            IT-KKBER.
   ENDLOOP.

Reward points if useful....

Regards

Re: join tables

Former Member — Thu, 13 Mar 2008 08:05:28 GMT

you can achieve it using internal tables. select 1st table and move it to internal table.

now use one more select with 'for all entries' in the internal table . make sure you dont write ur code with nested loop.

regards

Manoharan