Help required on Joins

[Former Member]

Hi,

Require some help on joining the below tables. I have the requirement as i pass Customer Number(KUNNR) and i need to get the below fields.Pls help me out in writing the code through joins for this.

Field Name Table Name

===========================

KUNNR KNA1

NAME1 ADRC

COUNTRY ADRC

REGION ADRC

CITY1 ADRC

TIME_ZONE ADRC

PERNR KNVP

NAME2 ADRC

STREET ADRC

POST_CODE1 ADRC

PO_BOX ADRC

POST_CODE2 ADRC

TEL_NUMBER ADRC

SMTP_ADDR ADR6

FAX_NUMBER ADRC

NAME1 KNVK

NAMEV KNVK

ANRED KNVK

LANGU ADRC

STCEG KNA1

TPLMA IFLOT

PLTXT IFLOT

BRSCH KNA1

BRAN1 KNA1

Below are the table joins.

KNA1-ADRNR = ADRC-ADRNR

KNA1-ADRNR = ADR6-ADDRNUMBER

KNA1-KUNNR = KNVK-KUNNR

KNA1-KUNNR = KNVV-KUNNR

KNA1-KUNNR = IFLOT-TPLMA

Regards,

Ramesh

[Former Member]

Select

into itab

from kna1 inner join adrc on kna1-adrnr = adrc-adrnr

inner join adr6 on kna1-adrnr = adr6-addrnumber

inner join knvk on kna1-kunnr = knvk-kunnr

inner join knvv on kna1-kunnr = knvv-kunnr

inner join iflot on KNA1-KUNNR = IFLOT-TPLMA

where

Regards

Anurag

[Former Member]

hi Ramesh,

Do this way ..

Select <fields>
   into itab
   from kna1 as a inner join 
        adrc as b 
   on a-adrnr = b-adrnr
  inner join adr6 as c
   on a-adrnr = c-addrnumber
  inner join knvk as d 
   on a-kunnr = d-kunnr
  inner join knvv as e 
   on a-kunnr = e-kunnr
  inner join iflot as  f
   on a-KUNNR = f-TPLMA
where <Conditons>

Regards,

Santosh

[Former Member]

Try

SELECT <fields> INTO CORRESPONDING FIELDS OF TABLE IT1

FROM ( ( ( ( KNA1 AS A INNER JOIN ADRC AS B

ON A_{ADRNR = B}ADRNR )

INNER JOIN ADR6 AS C ON A_{ADRNR = C}ADDRNUMBER )

INNER JOIN KNVK AS D ON A_{KUNNR = D}KUNNR)

INNER JOIN KNVV AS E ON A_{KUNNR = E}KUNNR)

INNER JOIN IFFLOT AS F ON A_{KUNNR = F}TPLMA)

WHERE <Condition>

Reward if Helpful

Regards

[Former Member]

select

kna1~KUNNR

adrc~NAME1

ADRC~COUNTRY

ADRC~REGION

ADRC~CITY1

ADRC~TIME_ZONE

knvp~PERNR

ADRC~NAME2

ADRC~STREEt

ADRC~POST_CODE1

ADRC~PO_BOX

ADRC~POST_CODE2

ADRC~TEL_NUMBER

ADR6~SMTP_ADDR

ADRC~FAX_NUMBER

knvk~NAME1

knvk~NAMEV

knvk~ANRED

ADRC~LANGU

kna1~STCEG

IFLOT~TPLMA

IFLOT~PLTXT

KNA1~BRSCH

KNA1~BRAN1

INTO TABLE IT_TAB

FROM KNA1 INNER JOIN ADRC ON KNA1-ADRNR = ADRC-ADRNR

KNA1 INNER JOIN ADR6 ON KNA1-ADRNR = ADR6-ADDRNUMBER

KNA1 INNER JOIN KNVK ON KNA1-KUNNR = KNVK-KUNNR

KNA1 INNER JOIN KNVV ON KNA1-KUNNR = KNVV-KUNNR

KNA1 INNER JOIN IFLOT ON KNA1-KUNNR = IFLOT-TPLMA

WHERE KNA1-KUNNR = YOUR PARAMETER (KUNNR).

Regards

Ashok P

[Former Member]

Hi Ashok,

When i am executing the code its giving as field KNA1-ADRNR is unknown. Could you pls let me know what exactly is missing in this.

Regards,

Ramesh,

[Former Member]

As we can see from the KNA1 structure the field does exist in the table !!!

Can you please check of your code again !

Regards

Anurag

[Former Member]

Hi,

Try Using FM 'ADDRESS_INTO_PRINTFORM' inspite of writing the SELECT Statement.

AJAY