Select St.

[Former Member]

Hi All ,

I have a select join st. like below , for this i need to add email id field into this table 'ITAB_DISPLAY' AND this can be obtained from table ADR6-smtp_addr and i the code no .'2' indictes tht , the requirment is to show all customers thought the email field is not filled . at the moment its displaying all customers who have e-mail id.part of this code is working correctly thought there r some missing email id for customers which r present in the table adr6.

1. select kna1~kunnr

kna1~land1

kna1~adrnr

knvv~vkorg

knvv~vkbur

INTO corresponding fields of table ITAB_DISPLAY

FROM kna1INNER JOIN

knvv ON knvv_{kunnr = kna1}kunnr

where kna1~kunnr IN s_kunnr

AND kna1~land1 IN s_land1

AND kna1~name1 IN s_name1

2. LOOP at itab_display.

CLEAR itab_data.

SELECT smtp_addr FROM adr6 INTO corresponding fields of table

itab_data where addrnumber = itab_display-adrnr.

append itab_data.

DELETE adjacent duplicates from itab_data.

LOOP at itab_data.

MOVE itab_data-smtp_addr TO itab_display-email.

MODIFY itab_display.

ENDLOOP.

ENDLOOP.

Any suggestions on this plss.....

Thxs,

Vind.

[anversha_s]

hi,

with 1 join query u can solve this.

data : begin og ITAB_DISPLAY occurs 0,

land1 .. ,

adrnr .. ,

vkorg .. ,

vkbur ..,

email .. ,

end of itab_display.

1. select kna1~kunnr

kna1~land1

kna1~adrnr

knvv~vkorg

knvv~vkbur

adr6~smtp_addr

INTO table ITAB_DISPLAY

FROM kna1

INNER JOIN

knvv

ON knvv_{kunnr = kna1}kunnr

INNER JOIN

adr6

on adr6_{addrnumber = kna1}adrnr

where kna1~kunnr IN s_kunnr

AND kna1~land1 IN s_land1

AND kna1~name1 IN s_name1.

rgds

anver

if hlped mark points

[Former Member]

Hi Anversha,

I already tried with the join st. as u told , its displaying only the customers who have an e-mail id , for customers who does have an e-mail id are been ignored which should be displayed as well in the grid ....

Thxs,

Vind.

[Former Member]

Select ADDRNUMBER, SMTP_ADDR into IT itab_Data.

loop at itab_display.

read table itab_data with key addrnumber = itab_display-adrnr.

if sy-subrc <> 0.

• itab_dispaly header will have customer details with no email id

endif.

endloop.

Message was edited by: Anupama Reddy

[Former Member]

Hello,

The first thing I tell is never use select statment inside a loop.

U can use select for all entries for selecting data from second table based on the first internal table.

Other thing is not clear,please explain once again.

Regards

[former_member404244]

Hi Vind,

data : v_index like sy-index.

select kna1~kunnr

kna1~land1

kna1~adrnr

knvv~vkorg

knvv~vkbur

INTO corresponding fields of table ITAB_DISPLAY

FROM kna1INNER JOIN

knvv ON knvv_{kunnr = kna1}kunnr

where kna1~kunnr IN s_kunnr

AND kna1~land1 IN s_land1

AND kna1~name1 IN s_name1

2. LOOP at itab_display.

CLEAR itab_data.

SELECT smtp_addr FROM adr6 INTO corresponding fields of table

itab_data where addrnumber = itab_display-adrnr.

append itab_data.

DELETE adjacent duplicates from itab_data.

ENDLOOP.

v_index = 1.

LOOP at itab_display.

read table itab_data with key <give field name which is common in both the tables>.I think it should be customer(kunnr).

if sy-subrc eq 0.

MOVE itab_data-smtp_addr TO itab_display-email.

MODIFY itab_display index v_index.

v_index = v_index + 1.

ENDLOOP.

try like this

Regards,

nagaraj

[Former Member]

Hi ,

change ur code as below

. select kna1~kunnr

kna1~land1

kna1~adrnr

knvv~vkorg

knvv~vkbur

INTO corresponding fields of table ITAB_DISPLAY

FROM kna1INNER JOIN

knvv ON knvv_{kunnr = kna1}kunnr

where kna1~kunnr IN s_kunnr

AND kna1~land1 IN s_land1

AND kna1~name1 IN s_name1

if not ITAB_DISPLAY[] is not initial.

2. LOOP at itab_display.

CLEAR itab_data.

SELECT single smtp_addr FROM adr6 INTO lv_address where addrnumber = itab_display-adrnr.

if sy-subrc eq 0.

itab_display-address = lv_address.

modify itab_display transporting address.

endif.

endloop.

DELETE adjacent duplicates from itab_data.

Regards

amole