INNER JOIN

[Former Member]

Hi all,

I need to join two database tables adrc and BUT020 and to display empty fields (e.g. house_num1) in ADRC by partner, rather than addrnumber. If an inner join is appropriate, how do I define it? Are there other ways.

[suredarreddy_pulimamidi]

don't use joins go for FOR ALL ENTRIES.

[Former Member]

Hi Darlighgton,

You have to use FOR ALL ENTRIES.

The other way is using join statement.

Thanks

Vkranth Khimavath

[Former Member]

Thanks a lot. Now here is a piece of code I wrote for the select statement.

SELECT house_num1 city1 street

FROM adrc

INTO TABLE jtab

FOR ALL ENTRIES IN it_but020

WHERE addrnumber = it_but020-addrnumber.

jtab is an internal table of type adrc and but020 is an internal table of type but020. There is no partner field in adrc, so i would like to display empty records for house_num1 for example, how then do i fit it in?

[Former Member]

Thanks a lot. Now here is a piece of code I wrote for the select statement.

SELECT house_num1 city1 street

FROM adrc

INTO TABLE jtab

FOR ALL ENTRIES IN it_but020

WHERE addrnumber = it_but020-addrnumber.

jtab is an internal table of type adrc and but020 is an internal table of type but020. There is no partner field in adrc, so i would like to display empty records for house_num1 by PARTNER for example, how then do i fit it in?

[Former Member]

If I'm to use inner join ,would it be correct to do the following if I'm to display empty fields of house_num1 by PARTNER:

DATA: BEGIN OF address,

partner TYPE but020-partner,

addrnumber TYPE adrc-addrnumber,

street TYPE adrc-street,

house_num1 TYPE adrc-house_num1,

city1 TYPE adrc-city1,

END OF address,

jtab LIKE STANDARD TABLE OF address WITH HEADER LINE.

SELECT but020_{partner adrc}addrnumber adrc~house_num1

INTO TABLE jtab

FROM but020

INNER JOIN adrc

ON but020_{addrnumber = adrc}addrnumber

where adrc~house_num1 = space.

if sy-dbcnt = 0.

uline.

write:/ 'There are no empty entries in the field: Address'.

uline.

else.

uline.

WRITE: / 'The Field: Address, has',SY-DBCNT,'empty fields for the following Business Partner entries:'.

uline.

LOOP AT jtab INTO address.

WRITE: / address-PARTNER.

ENDLOOP.

endif.

[varma_narayana]

Hi ..

Yes .. you can do this if you want to get the records where House_num is BLANK.

<b>reward if Helpful.</b>

[Former Member]

But it's not working as it should. It's extracting something totally different from what I expect.

[Former Member]

thanks all.