topic Re: Abap Report Query in Application Development and Automation Discussions

Abap Report Query

Former Member — Fri, 29 Feb 2008 13:53:22 GMT

Hi I need to retrive the following data from KNA1,KNVK,ADR6...

CustomerNo (KNA1-KUNNR)

CustomerName (KNA1-NAME1)

Contact Person(KNVK-NAMEV)

Address 1 (KNA1-STRAS)

City (KNA1-ORT01)

Country(KNA1-LAND1)

Postal Code(KNA1-PSTLZ)

Telephone No(KNA1-TELF1)

Fax No(KNA1-TELFX)

Email Address(ADR6-SMTP_ADDR)

Please let me know the various methods of retriveing the data (select query,joins etc....) and generate a abap report.

Please let me know how to use coma(,) seperator while using GUI_DOWNLOAD.

Regards,

Srini

Re: Abap Report Query

Former Member — Sat, 01 Mar 2008 12:01:57 GMT

***************************

select single name1

into itab_cust-p_name1

from knvk

where kunnr = itab_cust-kunnr.

*****************************************

select a_{kunnr a}name1 a_{stras a}ort01

a_{land1 a}pstlz a_{telf1 a}telfx

c~adrnr

from kna1 as a inner join adrc as b

on c_{ADRNR = d}ADDRNUMBER

into corresponding fields of table itab_cust

where a~kunnr in p_cust .

if this query is use then give point

Re: Abap Report Query

Former Member — Sat, 01 Mar 2008 14:13:10 GMT

Hi ,

You can use BAPI to get customer information :

BAPI_CUSTOMER_GETDETAIL.

You can use inner joins like:

KNA1 & KNVK having relationship with KUNNR field or etc.,

but ADR6 is doesnt have field with them. But Address number is existing both the tables KNVK & ADR6 table.

First you have to find the link to these tables. then

Define two internal tables, for performance wise.

1_table for KNA1 & KNVK fields.

2_table for KNVK field and ADR6 field.

select kna1~fields

knvk~fields from kna1

into table 1_table inner join knvk on kna1_{kunnr = knvk}kunnr

where kunnr in kunnr (select-option parameter).

if not 1_table[] is initial.

select adr6~ fields

knvk ~field (one field) - kunnr is good for furthor process

from adr6 into table 2_table inner join knvk on adr6_{addressnumber = knvk}addressnumber

for all entries in 1_table

where kunnr = 1_table-kunnr.

(you can reverse the tables and their fields according to your selection).

Just observe the above code clearly and understand. You should come to know that what needs to be done.

sekar

endif.

Re: Abap Report Query

Former Member — Sat, 01 Mar 2008 15:22:20 GMT

hi this is the good way ,

REPORT ZZZZZ .

tables :kna1,knvk,adr6.

data:begin of it_kna1 occurs 0,

kunnr like kna1-kunnr,

name1 like kna1-name1,

stras like kna1-stras,

ort01 like kna1-ort01,

land1 like kna1-land1,

pstlz like kna1-pstlz,

telf1 like kna1-telf1,

telfx like kna1-telfx,

end of it_kna1.

data: begin of it_knvk occurs 0,

kunnr like knvk-kunnr ,

namev like knvk-namev,

end of it_knvk.

data:begin of it_adr6 occurs 0,

PERSNUMBER like adr6-PERSNUMBER,

smtp_addr like adr6-smtp_addr,

end of it_adr6.

data:begin of it_final occurs 0,

kunnr like kna1-kunnr,

name1 like kna1-name1,

stras like kna1-stras,

ort01 like kna1-ort01,

land1 like kna1-land1,

pstlz like kna1-pstlz,

telf1 like kna1-telf1,

telfx like kna1-telfx,

namev like knvk-namev,

smtp_addr like adr6-smtp_addr,

end of it_final.

select kunnr

name1

stras

ort01

land1

pstlz

telf1

telfx

from kna1

into table it_kna1.

sort it_kna1 by kunnr .

if not it_kna1[] is initial.

select kunnr

namev

from knvk

into table it_knvk

for all entries in it_kna1

where kunnr = it_kna1-kunnr.

sort it_knvk by kunnr .

select PERSNUMBER

smtp_addr

from adr6

into table it_adr6

for all entries in it_kna1

where PERSNUMBER = it_kna1-kunnr.

sort it_adr6 by persnumber .

endif.

loop at it_kna1.

it_final-kunnr = it_kna1-kunnr.

it_final-name1 = it_kna1-name1.

it_final-stras = it_kna1-stras.

it_final-ort01 = it_kna1-ort01.

it_final-land1 = it_kna1-land1.

it_final-pstlz = it_kna1-pstlz.

it_final-telf1 = it_kna1-telf1.

it_final-telfx = it_kna1-telfx.

read table it_knvk with key kunnr = it_kna1-kunnr.

it_final-namev = it_knvk-namev.

read table it_adr6 with key PERSNUMBER = it_kna1-kunnr.

it_final-smtp_addr = it_adr6-smtp_addr.

append it_final.

clear it_final.

endloop.

loop at it_final.

write:/ it_final-kunnr ,

it_final-name1 ,

it_final-stras ,

it_final-ort01 ,

it_final-land1 ,

it_final-pstlz ,

it_final-telf1 ,

it_final-telfx ,

it_final-namev ,

it_final-smtp_addr .

endloop.

we can do this with joins but joins are not suggestable .

reward points if useful,

venkat.