Pls advice on this select statment...

[Former Member]

HI,

I want to display the fields in tcode xd02 with sort functionality with fields

kunnr kna1 table

name1 kna1

SMTP_ADDR email id from table ADR6

my question is on mapping

from kna1 we can get field kunnr and name1

and adrnr and this adrn is used to fetch amtp_addr from adr6

can anyoe suggest on select statements link as table kna1 has one unique kunnr

but it can have multiple name1 and adrnr

so how to pick the kunnr ,name 1 and adrnr from table kna1 and map to

adr6 table

as witinn the table kna1 there can be multiple name1 related to one single kunnr

so wil it be like

select kunnr from kna1..into it_kunnr

then select nam1 and adrnr from table kna2 for all entrirs in It_kunnr

or first select kunnr name1 from kna1 into table it_kunnr

pls suggest an appropiat select statament

or directly select select kunnr name1 adrnr from table kna1 inot it_kna1

and then based on adrnr fetch email id from adr6

pls suggest

and i suppose this can be achevied by alv report

regards

Arora

[Former Member]

select kunnr from kna1..into it_kunnr

then select nam1 and adrnr from table kna2 for all entrirs in It_kunnr

This statement will do,else use join for table kna1 and adrnr

Regards,

Reema

[Former Member]

hi Reema

pls read my query again...your reply will confuse others reply

firstly there is no kna2 table and no adrnr table

adrnr , kunnr and name1 are a field in kna1 table and linked to table adr6 with adrnr

i want to select name1 and kunnr from kna1 and then email id from adr6 table and wrothe my concren in above query fully

regards

Arora

[Former Member]

Use this code:

select a~adrnr
       a~kunnr
       a~name1
       b~smtp_addr
       into table lt_cust
       from kna1 as a join adr6 as b
       on a~adrnr eq b~adrnr
       where <condition>.

Please mark points if the solution was useful.

Regards,

Manoj

[Former Member]

I just copied the statement stated in your mail....If you go thru ur query ,

Regards,

Reema..

Message was edited by:

Reema Elsy Easow

Message was edited by:

Reema Elsy Easow

[Former Member]

Hi manoj

The adrnr can be more than one for kunnr and name1 so will this join give problem on that

regards

Arora

[Former Member]

How come.. Check the table KNA1.. KUNNR is the only key field there. For one KUNNR there can be ONLY ONE NAME1 and ADRNR in the table.

Please mark points if the solution was useful.

Regards,

Manoj

[Former Member]

hi manoj

ya i got confused somehow with the adrnr

now my q is

1) if i use for all entries statement then how to get smpt_addr(email id) from ADR6 table as though adrnr is primary key in ADR6 table but one kunnr can have multiple email ids

how to loop and display join is straight forward it will display the fields kunnr name1 and email id

but my q is for if i am using for all entries in that..pls sugget just for knowldge sake

regards

Arora

[Former Member]

Hi Nishant

first get the data from kna1 table

select kunnr name1 adrnr from kna1 into table it_kna1.

now get the addr from adr66 table ok.

if it_kna1[] is not initail.

select smtp_addr adrnr into table it_addr for all entries in it_kna1 where adrnr eq it_kna1-adrnr.

and now move the data to final intenal table.

loop at it_kna1.

read table it_addr with key adrnr = it_kna1-adrnr binary search.

it_final-kunnr = it_kna1-kunnr.

etc.

append it_final.

clear it_final,it_kna1,it_addr.

endloop.

reward points to all helpful answers

kiran.M

[Former Member]

hi kiran

what about duplicate adrnr from table kna1 as it is not a primary key?

and also name1 can be duplicated as kunnr is the only uniqu field in table kna1?

regards

Arora

[Former Member]

Ya

sorry i forget it before using for all entries u must sort and remove the duplicate keys

loop at it_kna1.

it_check-adrnr = it_kna1-adrnr.

append it_check.

clear

endllop.

sort it_check.

delete adjacent duplicates from it_check.

if it_check[] is not initail.

select smtp_addr adrnr into table it_addr for all entries in it_check where adrnr eq it_check-adrnr.

endif.

[Former Member]

hi Kiran

pls let me know how to display the data

as now we have two internal tables

regards

Arora

[Former Member]

theread closed

[Former Member]

hI nishant

u have a common field in both of internal tables

so get the two tables data into one int table and display it

revert back

please reward points for helpful ans's

kiran.M

[Former Member]

hi Kiran

pls can u eloborate

how to do so i need the code as t here are some confusion in my mindregarding this

can u pls paste all code from s tart to end as the requirment i already told

i dotn want join statement but by the second method

ie my aim is to display kunnr name1 from kna1 and email id from Adr6 table...

regards

Arora

[Former Member]

need the code as stated in earlier comment