topic Re: classical report in Application Development and Automation Discussions

classical report

Former Member — Fri, 17 Apr 2009 10:08:17 GMT

hi friends.

what is wrong in this logic?

6666( kunnr ) is not available in vbak but available in kna1 eventhough it is displaying in the output as like few more records also displaying.

i have to generate output list where kna1-kunnr = vbak-kunnr.

tables : vbak,kna1 .

data : begin of itab occurs 0,

kunnr like vbak-kunnr,

adrnr like kna1-adrnr,

land1 like kna1-land1,

name1 like kna1-name1,

name2 like kna1-name2,

ort01 like kna1-ort01,

pstlz like kna1-pstlz,

regio like kna1-regio,

telf1 like kna1-telf1,

telfx like kna1-telfx,

end of itab.

select-options : s_kunnr for vbak-kunnr.

select vbak_{kunnr kna1}adrnr kna1_{land1 kna1}name1 kna1_{name2 kna1}ort01 kna1_{pstlz kna1}regio kna1_{telf1 kna1}telfx

into table itab

from vbak

inner join

kna1 on vbak_{kunnr = kna1}kunnr where vbak~kunnr in s_kunnr.

loop at itab.

write : /10 itab-kunnr,25 itab-adrnr,40 itab-land1,60 itab-name1,80 itab-name2,100 itab-ort01,120 itab-pstlz,140 itab-regio, 160 itab-telf1, 180 itab-telfx.

endloop.

can anyone suggest.

thanks&regards

deepika.

Re: classical report

Former Member — Fri, 17 Apr 2009 10:11:34 GMT

Try the Inner Join condition in the reverse way i.e.

kna1_{kunnr = vbak}kunnr

Re: classical report

Former Member — Fri, 17 Apr 2009 10:14:24 GMT

thank you for your reply.

yes i have reversed it is not working out.

thanks&regards

deepika.

Re: classical report

Former Member — Fri, 17 Apr 2009 10:22:15 GMT

Use Left Outer Join in you query.

select vbak~kunnr kna1~adrnr kna1~land1 kna1~name1 kna1~name2 kna1~ort01 kna1~pstlz kna1~regio kna1~telf1 kna1~telfx
into table itab
from vbak
LEFT OUTER JOIN
kna1 on vbak~kunnr = kna1~kunnr where vbak~kunnr in s_kunnr.

Regards,

Gurpreet

Re: classical report

awin_prabhu — Fri, 17 Apr 2009 10:22:53 GMT

I think table 'VBAK' is having duplicate entries for KUNNR.

So after selecting use,

DELETE ADJACENT DUPLICATES FROM itab.

Re: classical report

Former Member — Fri, 17 Apr 2009 10:25:18 GMT

Hi,

Try this:

select a_{kunnr b}adrnr b_{land1 b}name1 b_{name2 b}ort01 b_{pstlz b}regio b_{telf1 b}telfx

into table itab

from vbak as a

inner join kna1 as b on a_{kunnr = b}kunnr

where a~kunnr in s_kunnr.

Hope it helps.

Regards.

Sandra Marques

Re: classical report

Former Member — Fri, 17 Apr 2009 10:29:11 GMT

it is not working even i use left outer join.

I guess if we put left outer join in case of document if it is present in any one of the table,it should be displayed as i know.

but my case is to display the data where kna1-kunnr = vbak-kunnr.

thanks&regards

deepika.

Re: classical report

Former Member — Fri, 17 Apr 2009 10:31:35 GMT

no.it is not working sandra.

thanks&regards

deepika.

Re: classical report

Former Member — Fri, 17 Apr 2009 10:38:37 GMT

Left Outer Join will Take always the Left Table into Consideration first.Here Vbak Should be Check for existence and then the data from kna1 should be Picked which is not working.May be the data in the DBtable in no appropriate.

Also Try FOR ALL ENTRIES.

Ex:

1) Itab will contain KUNNR.
2) ITAB1 will contain KUNNR,adrnr ,land1 ,name1 ,name2 ,ort01,pstlz ,regio ,telf1 ,telfx

select kunnr 
into table itab
from vbak
kunnr in s_kunnr.

IF sy-subrc = 0.
SELECT KUNNR,adrnr ,land1 ,name1 ,name2 ,ort01,pstlz ,regio ,telf1 ,telfx
                 From KNA1
                 INTO TABLE ITAB1
                 FOR ALL ENTRIES IN ITAB
                 WHERE KUNNR = ITAB-KUNNR.
ENDIF.

Regards,

Gurpreet

Re: classical report

Former Member — Fri, 17 Apr 2009 10:41:17 GMT

hi,

If KUNNR exist in VBAk then only it will display based on condition .

Can yu recheck in VBAK table for the existence of KUNNR (6666).

wat ever the statement yu have written is perfect.

regards,

Naveen

Re: classical report

Former Member — Fri, 17 Apr 2009 10:43:46 GMT

Hi,

Try using this select statement, this shd work

Select a~kunnr

b~adrnr

b~land1

b~name1

b~name2

b~ort01

b~ pstlz

b~regio

b~telf1

b~telfx

from vbak as a INNER JOINT

kna1 as b on

a_{kunnr = b}kunnr

into table itab

where a~kunnr in s_kunnr

Thanks and Regards

Suraj S Nair

Re: classical report

former_member609118 — Fri, 17 Apr 2009 10:48:02 GMT

Hi,

Code is absolutely right , data might not be available for the range given.

Re: classical report

Former Member — Fri, 17 Apr 2009 10:48:56 GMT

yes i have checked 6666 kunnr is available in kna1 .but vbak does not have this customer ..eventhough it is displaying.

thanks&regrs

deepika

Re: classical report

Former Member — Fri, 17 Apr 2009 11:01:53 GMT

can yu write a simple select stamemnt on VBAK where Kunnr = '6666' and check if any data is being selected.

Regards,

Naveen

Re: classical report

Former Member — Fri, 17 Apr 2009 11:49:42 GMT

'6666' customer is not there in vbak table.

Re: classical report

Former Member — Fri, 17 Apr 2009 11:57:06 GMT

Try to Use FOR ALL ENTRIES in your select Query i am sure it would resolve the issue.

Just Check the Code in the Last Post and try it to check whether the data is Coming Correctly.

Codes from the LAST POST.

select vbak~kunnr kna1~adrnr kna1~land1 kna1~name1 kna1~name2 kna1~ort01 kna1~pstlz kna1~regio kna1~telf1 kna1~telfx
into table itab
from vbak
LEFT OUTER JOIN
kna1 on vbak~kunnr = kna1~kunnr where vbak~kunnr in s_kunnr.

Regards,

Gurpreet

Re: classical report

Former Member — Mon, 20 Apr 2009 13:41:16 GMT

Try this one

select vbak_{kunnr kna1}adrnr kna1_{land1 kna1}name1 kna1_{name2 kna1}ort01 kna1_{pstlz kna1}regio kna1_{telf1 kna1}telfx

into table itab

from kna1

inner join

vbak on kna1_{kunnr = vbak}kunnr where vbak~kunnr in s_kunnr.

After from keyword write "kna1", because kunnr is the primary key in kna1 table. I think it might work.

Re: classical report

Former Member — Tue, 21 Apr 2009 11:56:56 GMT

Hi Deepika,

Whats the answer.

Regards,

Bhanu prakash