classical report

[Former Member]

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.

[Former Member]

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

kna1_{kunnr = vbak}kunnr

[Former Member]

thank you for your reply.

yes i have reversed it is not working out.

thanks&regards

deepika.

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[awin_prabhu]

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

So after selecting use,

DELETE ADJACENT DUPLICATES FROM itab.

[Former Member]

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

[Former Member]

no.it is not working sandra.

thanks&regards

deepika.

[Former Member]

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

[Former Member]

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

thanks&regrs

deepika

[Former Member]

hi

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

Regards,

Naveen

[Former Member]

'6666' customer is not there in vbak table.

[Former Member]

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

[Former Member]

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

[former_member609118]

Hi,

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

[Former Member]

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.

[Former Member]

Hi Deepika,

Whats the answer.

Regards,

Bhanu prakash