select statement problem

[Former Member]

hi all,

im doing the sales report in which i want all the customers from master doesnt matter even if there is sale against them ...but im taking the VBRK fst then taking data from master bcoz if im taking master as base then it goes to VBRK and it takes lot of execution time. like.

select kunrg vbeln fkdat vbtyp vkorg vtweg into
  (itab-kunnr,itab-vbeln,itab-fkdat,itab-vbtyp,itab-vkorg,itab-vtweg)
from vbrk
  where kunrg in kunnr and fkdat in fkdat and fksto ne 'X' and
  fkart <> 'ZRO' and vkorg in vkorg and vtweg in vtweg and spart in
 spart.


   select matnr matkl fkimg brgew kzwi6 netwr into
   (itab-matnr,itab-matkl,itab-fkimg,itab-brgew,itab-kzwi6,
   itab-netwr)
   from vbrp
   where vbeln = itab-vbeln.


select name1 vkorg vtweg spart vwerk bzirk kdgrp vkbur into
 (itab-name1,itab-vkorg,itab-vtweg,itab-spart,itab-vwerk,
itab-bzirk,itab-kdgrp,itab-vkbur)
 from kna1vv
 where kunnr in kunnr and vkorg in vkorg and vtweg in vtweg
and spart in spart and kunnr = itab-kunnr.


  check sy-subrc = 0.


append itab.
clear itab.

endselect.
endselect.
endselect.

the problem here is that for each entry in VBRK it displays all cust. from master again and again every time .. i want to avoid this ..

so plzz help me out..

thanxxx....

[Former Member]

Hi,

Select within select is not recommendable,it is costly statement.

This is the reason why it is taking time.

Use for all entries for second internal table looping.

Use internal table instead of writing individual workareas in into clause.

Edited by: Sreesudha Gullapalli on Feb 6, 2009 9:37 AM

[Former Member]

>

> Hi,
> Select within select is not recommendable,it is costly statement.
> This is the reason why it is taking time.
> Use for all entries for second internal table looping.
> Use internal table instead of writing individual workareas in into clause.
> 
> Edited by: Sreesudha Gullapalli on Feb 6, 2009 9:37 AM

can u give me code for that..???

[Former Member]

Hi,

You can use inner join query. You can use the code below as a guide.

SELECT A_{lifnr B}werks A_{ebeln B}ebelp A_{bukrs A}zterm A~ekorg

A_{ekgrp A}aedat A_{waers A}kdate A_{inco1 A}inco2 A~adrnr

B_{matnr B}menge B_{meins B}netpr B_{netwr C}eindt B~mwskz

B~txz01

INTO TABLE tb_po

FROM ekko as A INNER JOIN ekpo as B

ON A_{ebeln = B}ebeln

INNER JOIN eket as C

ON ( B_{ebeln = C}ebeln AND B_{ebelp = C}ebelp )

WHERE A~ebeln IN so_ebeln

AND A~bukrs = p_bukrs

AND A~aedat IN so_aedat

AND A~lifnr IN so_lifnr

AND A~ekorg = p_ekorg

AND A~ekgrp = p_ekgrp

AND A~bsart = p_bsart

AND B~loekz NE 'L'

AND ( A_{frgke = 'Q' or A}frgke = ' ' ).

Regards,

Mon Magallanes

[Former Member]

Hi Nilesh ,

Yes a better option is to go for joins (inner or outer) and then validate your entries such that when the record doesnt exist display an error messgae or something .

That you can do it using sy-subrc .

Here s how you can do it using joins and for all entries

DATA:

t_mara LIKE STANDARD TABLE OF fs_material,

t_marc LIKE STANDARD TABLE OF fs_material1.

SELECT matnr

mtart

INTO CORRESPONDING FIELDS OF TABLE t_mara

FROM mara

WHERE mtart = p_mtart .

• AND EXISTS ( SELECT marc~werks

• INTO CORRESPONDING FIELDS OF TABLE

• t_material FROM marc ).

SELECT matnr

werks

FROM marc

INTO TABLE t_marc FOR ALL ENTRIES

IN t_mara

WHERE matnr = t_mara-matnr.

Hope it helps!

Much Regards ,

Amuktha .

Edited by: Amuktha Naraparaju on Feb 6, 2009 9:44 AM

[Former Member]

Hi,

try this

declare a structure with vbrk and vbrp table fields.

select a~kunrg

a_{vbeln a}fkdat a_{vbtyp a}vkorg a~vtweg

b_{matnr b}matkl b_{fkimg b}brgew b_{kzwi6 b}netwr

into table itab

from vbrk as a inner join vbrp as b

where a_{kunrg in kunnr and a}fkdat in fkdat and a~fksto ne 'X' and

a~ fkart 'ZRO' and a_{vkorg in vkorg and a}vtweg in vtweg and a~spart in

spart and vbeln = itab-vbeln.

[former_member404244]

Hi,

Try with this code

select kunrg

vbeln

fkdat

vbtyp

vkorg

vtweg into table itab

from vbrk

where kunrg in kunnr and fkdat in fkdat and fksto ne 'X' and

fkart 'ZRO' and vkorg in vkorg and vtweg in vtweg and spart in

spart.

if not itab[] is initial.

select matnr matkl fkimg brgew kzwi6 netwr into table itab1

from vbrp for all entries in itab

where vbeln = itab-vbeln.

select name1 vkorg vtweg spart vwerk bzirk kdgrp vkbur into

from kna1vv for all entries in itab

where kunnr = itab-kunnr and vkorg in vkorg and vtweg in vtweg

and spart in spart.

endif.

Regards,

Nagaraj

[Former Member]

Hi,

In last select you can avoid name1 .

Regards

Pinaki

[Former Member]

try thi:

select a~kunrg a~vbeln a~fkdat a~vbtyp a~vkorg a~vtweg
       b~matnr b~matkl b~fkimg b~brgew b~kzwi6 b~netwr
      c~name1 c~vkorg c~vtweg c~spart c~vwerk c~bzirk c~kdgrp c~vkbur
                INTO CORRESPONDING FIELDS OF TABLE itab
         FROM ( ( vbrk AS a
         INNER JOIN vbrp AS b ON a~vbeln   = b~vbeln  )
                  INNER JOIN kna1vv AS c ON c~kunnr = a~kunnr  )
                  where a~kunrg in kunnr and a~fkdat in fkdat and a~fksto ne 'X' and
                   a~fkart = 'ZRO' and a~vkorg in vkorg and a~vtweg in vtweg and a~spart in
                 spart and c~kunnr in kunnr and c~vkorg in vkorg and c~vtweg in vtweg
                 and c~spart in spart.

here itab contains all the fields

this is inner join on 3 tables

[Former Member]

>

> Hi,
> In last select  you  can avoid  name1 .
> Regards
> Pinaki

this will solve my prob..?????

[Former Member]

Hi Nilesh,

Try below code, i m sure it will work for you..

DATA : BEGIN OF IT_KNA1 OCCURS 0,
        KUNNR LIKE KNA1VV-KUNNR,
        NAME1 LIKE KNA1VV-NAME1,
        SPART LIKE KNA1VV-SPART ,
        VWERK LIKE KNA1VV-VWERK,
        BZIRK LIKE KNA1VV-BZIRK ,
        KDGRP LIKE KNA1VV-KDGRP,
        VKBUR LIKE KNA1VV-VKBUR,
       END OF IT_KNA1.

DATA : BEGIN OF IT_VBRK OCCURS 0,
        VBELN LIKE VBRK-VBELN ,
        KUNRG LIKE VBRK-KUNRG ,
        FKDAT LIKE VBRK-FKDAT ,
        VBTYP LIKE VBRK-VBTYP ,
        VKORG LIKE VBRK-VKORG ,
        VTWEG LIKE VBRK-VTWEG,
       END OF IT_VBRK.

DATA : BEGIN OF IT_VBRP OCCURS 0,
        VBELN LIKE VBRP-VBELN ,
        POSNR LIKE VBRP-POSNR ,
        MATNR LIKE VBRP-MATNR ,
        MATKL LIKE VBRP-MATKL ,
        FKIMG LIKE VBRP-FKIMG ,
        BRGEW LIKE VBRP-BRGEW ,
        KZWI6 LIKE VBRP-KZWI6 ,
        NETWR LIKE VBRP-NETWR,
       END OF IT_VBRP.

DATA : BEGIN OF IT_FINAL OCCURS 0,
        KUNNR LIKE KNA1VV-KUNNR,
        NAME1 LIKE KNA1VV-NAME1,
        SPART LIKE KNA1VV-SPART ,
        VWERK LIKE KNA1VV-VWERK,
        BZIRK LIKE KNA1VV-BZIRK ,
        KDGRP LIKE KNA1VV-KDGRP,
        VKBUR LIKE KNA1VV-VKBUR,
        VBELN LIKE VBRK-VBELN ,
        VBELP LIKE VBRP-POSNR ,
        KUNRG LIKE VBRK-KUNRG ,
        FKDAT LIKE VBRK-FKDAT ,
        VBTYP LIKE VBRK-VBTYP ,
        VKORG LIKE VBRK-VKORG ,
        VTWEG LIKE VBRK-VTWEG,
        MATNR LIKE VBRP-MATNR ,
        MATKL LIKE VBRP-MATKL ,
        FKIMG LIKE VBRP-FKIMG ,
        BRGEW LIKE VBRP-BRGEW ,
        KZWI6 LIKE VBRP-KZWI6 ,
        NETWR LIKE VBRP-NETWR,
       END OF IT_FINAL.

SELECT KUNNR NAME1 SPART VWERK BZIRK KDGRP VKBUR
INTO TABLE IT_KNA1
FROM KNA1VV
WHERE KUNNR IN KUNNR AND
      VKORG IN VKORG AND
      VTWEG IN VTWEG AND
      SPART IN SPART.

IF IT_KNA1[] IS NOT INITIAL.
  SELECT VBELN KUNRG FKDAT VBTYP VKORG VTWEG
  INTO  TABLE IT_VBRK
  FROM VBRK
  FOR ALL ENTRIES IN IT_KNA1
  WHERE KUNRG =  IT_KNA1-KUNNR AND
        FKDAT IN FKDAT   AND
        FKSTO NE 'X'     AND
        FKART EQ 'ZRO'   AND
        VKORG IN VKORG   AND
        VTWEG IN VTWEG   AND
        SPART IN SPART.
  IF IT_VBRK[] IS NOT INITIAL.
    SELECT VBELN POSNR MATNR MATKL FKIMG BRGEW KZWI6 NETWR
    INTO TABLE IT_VBRP
    FROM VBRP
    FOR ALL ENTRIES IN IT_VBRK
    WHERE VBELN = IT_VBRK-VBELN.
  ENDIF.
  
  LOOP AT IT_KNA1.
    MOVE-CORRESPONDING IT_KNA1 TO IT_FINAL.
    READ TABLE IT_VBRK WITH KEY KUNRG = IT_KNA1-KUNNR.
    IF SY-SUBRC = 0.
      LOOP AT IT_VBRK WHERE KUNRG = IT_KNA1-KUNNR.
        MOVE-CORRESPONDING IT_VBRK TO IT_FINAL.
        LOOP AT IT_VBRP WHERE VBELN = IT_VBRK-VBELN.
          MOVE-CORRESPONDING IT_VBRP TO IT_FINAL.
          APPEND IT_FINAL.
          CLEAR IT_FINAL.
        ENDLOOP.
      ENDLOOP.
    ELSE.
      APPEND IT_FINAL.
      CLEAR IT_FINAL.
    ENDIF.
  ENDLOOP.

ENDIF.

Regards,

Meet

[Former Member]

HI,

You will have to add some more fields in the join of kna1vv. This is because the customer can be maintained for many sales areas and you should look for the customer only under a specific sales area. thus change your query to :

SELECT
a~kunrg a~vbeln a~fkdat a~vbtyp a~vkorg a~vtweg
b~matnr b~matkl b~fkimg b~brgew b~kzwi6 b~netwr
c~name1 c~spart c~vwerk c~bzirk c~kdgrp c~vkbur
INTO TABLE itab
FROM vbrk AS a INNER JOIN vbrp AS b ON b~vbeln = a~vbeln
               INNER JOIN kna1vv AS c ON c~kunnr = a~kunrg and
                                         c~VKORG = a~VKORG and  
                                         c~VTWEG = a~VTWEG and
                                         c~SPART = a~SPART
WHERE
a~kunrg IN kunnr AND
a~fkdat IN fkdat AND
a~fksto NE 'X' AND
a~fkart EQ 'ZRO' AND
a~vkorg IN vkorg AND
a~vtweg IN vtweg AND
a~spart IN spart.

Now the customer record that is valid for the VBRK record will be fetched.

regards,

Advait

[Former Member]

>

> HI,
> You will have to add some more fields in the join of kna1vv. This is because the customer can be maintained for many sales areas and you should look for the customer only under a specific sales area. thus change your query to :
> 
> 
> 

> SELECT
> a~kunrg a~vbeln a~fkdat a~vbtyp a~vkorg a~vtweg
> b~matnr b~matkl b~fkimg b~brgew b~kzwi6 b~netwr
> c~name1 c~spart c~vwerk c~bzirk c~kdgrp c~vkbur
> INTO TABLE itab
> FROM vbrk AS a INNER JOIN vbrp AS b ON b~vbeln = a~vbeln
>                INNER JOIN kna1vv AS c ON c~kunnr = a~kunrg and
>                                          c~VKORG = a~VKORG and  
>                                          c~VTWEG = a~VTWEG and
>                                          c~SPART = a~SPART
> WHERE
> a~kunrg IN kunnr AND
> a~fkdat IN fkdat AND
> a~fksto NE 'X' AND
> a~fkart EQ 'ZRO' AND
> a~vkorg IN vkorg AND
> a~vtweg IN vtweg AND
> a~spart IN spart.
> 
> 
> 
> Now the customer record that is valid for the VBRK record will be fetched.
> 
> regards,
> Advait

i want ALL THE RECORDS from MASTER they r matching or not.....

i have aslready mentioned this before....

thanx......

[SuhaSaha]

Hello NIlesh,

Try this code using INNER JOINS:

DATA:

BEGIN OF itab OCCURS 0,
kunnr TYPE kunnr,
vbeln TYPE vbeln_vf,
fkdat TYPE fkdat,
vbtyp TYPE vbtyp,
vkorg TYPE vtweg,
matnr TYPE matnr,
matkl TYPE matkl,
fkimg TYPE fkimg,
brgew TYPE brgew_15,
kzwi6 TYPE kzwi6,
netwr TYPE netwr_fp,
name1 TYPE name1,
spart TYPE spart,
vwerk TYPE vwerk,
bzirk TYPE bzirk,
kdgrp TYPE kdgrp,
vkbur TYPE vkbur,
END OF itab.

SELECT
a~kunrg a~vbeln a~fkdat a~vbtyp a~vkorg a~vtweg
b~matnr b~matkl b~fkimg b~brgew b~kzwi6 b~netwr
c~name1 c~spart c~vwerk c~bzirk c~kdgrp c~vkbur
INTO TABLE itab
FROM vbrk AS a INNER JOIN vbrp AS b ON b~vbeln = a~vbeln
               INNER JOIN kna1vv AS c ON c~kunnr = a~kunrg
WHERE
a~kunrg IN kunnr AND
a~fkdat IN fkdat AND
a~fksto NE 'X' AND
a~fkart EQ 'ZRO' AND
a~vkorg IN vkorg AND
a~vtweg IN vtweg AND
a~spart IN spart.

IF sy-subrc = 0.
ENDIF.

Hope this helps.

BR,

Suhas

[Former Member]

even if im joining VBRK and VBRP but the thing is that they will have the same o/p it will not solve the prob..

the prob is for every entry from VBRK and VBRP join it shows all the cust. from Masterit shld go into masters table only once and not for every record......

thanx.....

[Former Member]

thanxx but not the single code from the above replies is working fine .....

thanxx...

[Former Member]

I think you have managed to confuse us everyone -:). Your requirements are not clear , may be it is better to put an example and explain what you are expecting.

I dont understand , if you need customer master data why would you search VBRK and VBRP ?

Anyways , if you can avoid using the above tables, then try to use Delete adjacent duplicates by comparing the fields for which you want a unique record to be displayed. Do this after the select.

regards,

Advait

[Former Member]

Hi Nilesh,

Try to replace your code with mine,

it will work fine for you..

I think you need to add / remove some fields in tables but method will be run as per your requirement..

As you need all datas from customer master and based on this you need data from VRK and VBRP,

from this code you will able to get it..

Regards,

Meet

[Former Member]

>

> I think you have managed to confuse us everyone -:). Your requirements are not clear , may be it is better to put an example and explain what you are expecting.
> 
> I dont understand , if you need customer master data why would you search VBRK and VBRP ? 
> 
> Anyways , if you can avoid using the above tables, then try to use Delete adjacent duplicates by comparing the fields for which you want a unique record to be displayed. Do this after the select.
> 
> regards,
> Advait

c im not confused at all and i dont want to ...

thats why i paste my code also there ...so that all have clear idea...

and i dont just want the cust master data if i wanted that i wolud used VBRK and VBRP

again im explaining

like ..

cust sales org. mat date

1100 s1 m1 mmyy

1100 s2

1100 s3 m2 mmyy

here cust from s2 sales org didnt have sale so its blank and this can be available from master only

THATS WHY IM USING CUST MASTER...

and other 2 recds r getting me from VBRK & VBRP

but the code which i have given is giving me correct records but only the prob of appending

the master records are eppending each time for every record from VBRK and VBRP

i think now u ppl should get....

[Former Member]

>

> Hi Nilesh,
> 
> Try to replace your code with mine,
> it will work fine for you..
> I think you need to add / remove some fields in tables but method will be run as per your requirement..
> As you need all datas from customer master and based on this you need data from VRK and VBRP,
> from this code you will able to get it..
> 
> Regards,
> Meet

plzzzz give me the final exact code. u can do modification in my code also that will be good for u and mee too.

thanx...

Edited by: NILESH S. on Feb 6, 2009 11:05 AM

[Former Member]

Aha, now it is clear.

May be you should change your join strategy a bit. Try to start your query with first selecting data from KNA1KVV and join the vbrk and vbrp to it.

regards,

Advait

[Former Member]

>

> Aha, now it is clear.
> 
> May be you should change your join strategy a bit. Try to start your query with first selecting data from KNA1KVV and join the vbrk and vbrp to it.
> 
> regards,
> Advait

i already done that but its taking long time to execute for even single date.

so i decide to take it from VBRK ...

[Former Member]

why no reply now..???

u ppl were confused wanted me to explain the requirment. i explain it again and again and now no one has the answer.????

[Former Member]

Hi Nilesh,

Pls refer the code which i have given above..

Also tell me what's problem after implementing it...

Regards,

Meet

[Former Member]

 LOOP AT IT_KNA1.
    MOVE-CORRESPONDING IT_KNA1 TO IT_FINAL.
    READ TABLE IT_VBRK WITH KEY KUNRG = IT_KNA1-KUNNR.
    IF SY-SUBRC = 0.
      LOOP AT IT_VBRK WHERE KUNRG = IT_KNA1-KUNNR.
        MOVE-CORRESPONDING IT_VBRK TO IT_FINAL.
        LOOP AT IT_VBRP WHERE VBELN = IT_VBRK-VBELN.
          MOVE-CORRESPONDING IT_VBRP TO IT_FINAL.
          APPEND IT_FINAL.
          CLEAR IT_FINAL.
        ENDLOOP.
      ENDLOOP.
    ELSE.
      APPEND IT_FINAL.
      CLEAR IT_FINAL.
    ENDIF.
  ENDLOOP.

Clear statement: this is not much better than the original coding, can absolutely not be recommended!

The loop inisde other loops MUST use either special tables or binary search!

BUT the original problem is actually looking for a solution with a join. I can not be so hard to find this solution.

Siegfried

[Former Member]

Hi nilesh,

I found in your code, that u r using select and endselect.

which will hit database very much. here i am writing a code assuming that u were getting right result but it was very slow. so i am doing performance tunning of your code here.

data: i_vbrk type standard table of vbrk with header line.

data: i_vbrp type standard table of vbrp with header line.

data: i_kna1vv type standard table of kna1vv with header line.

*-create an internal table final which will contain result--

*- i am assuming here it it ITAB

select kunrg vbeln fkdat vbtyp vkorg vtweg into

(i_vbrk-kunnr,i_vbrk-vbeln,i_vbrk-fkdat,i_vbrk-vbtyp,i_vbrk-vkorg,i_vbrk-vtweg)

from vbrk into table vbrk

where kunrg in kunnr

and fkdat in fkdat

and fksto ne 'X'

and fkart = 'ZRO'

and vkorg in vkorg

and vtweg in vtweg

and spart in spart.

*if record is very less in i_vbrk then u can use select for all entries-

*-vbeln is a primary of both table so it will not give more effect so go for select for all entries

if i_vbrk[] is not initial.

select matnr matkl fkimg brgew kzwi6 netwr into

(i_vbrp-matnr,i_vbrp-matkl,i_vbrp-fkimg,i_vbrp-brgew,i_vbrp-kzwi6,

i_vbrp-netwr)

from vbrp into table i_vbrp

for all entries in i_vbrk

where vbeln = i_vbrk-vbeln.

endif.

sort i_vbrk by vbeln.

sort i_vbrp by vbeln.

data: index type sy-tabix.

index = 1.

loop at i_vbrk.

loop at i_vbrp from index.

if i_vbrk-vbeln = i_vbrp-vbeln.

*---map all records to ITAB -from i_vbrk and i_vbrp then append it into ITAB---

append ITAB.

index = sy-tabix.

exit.

endif.

clear itab.

endloop.

select name1 vkorg vtweg spart vwerk bzirk kdgrp vkbur into

(i_kna1vv-name1,i_kna1vv-vkorg,i_kna1vv-vtweg,i_kna1vv-spart,i_kna1vv-vwerk,

i_kna1vv-bzirk,i_kna1vv-kdgrp,i_kna1vv-vkbur)

from kna1vv into table kna1vv

where kunnr in kunnr

and vkorg in vkorg

and vtweg in vtweg

and spart in spart.

if sy-subrc = 0.

clear index.

index = 1.

sort itab by kunnr.

sort i_kna1vv by kunnr.

index = 1.

loop at i_kna1vv.

loop at itab from index.

if itab-kunnr = i_knvv1-vbeln.

*---map all records to ITAB -from i_kna1vv then modify ITAB---

modify ITAB.

index = sy-tabix.

exit.

endif.

clear itab.

endloop.

i think i will solve your problem.

and let me know.

endif.

[Former Member]

*I think there is one total confusion here! The forum is notr expected to give anybody a final

working coding, the is always the task of the guy who asks!

It gives recommendations and hints, and you are expected to learn and figure out the last steps,

especially you are expected to run the coding checks.

Overall, I would expect that you are able to solve a similar problem by yourself the next time*

I still don't understand what you actually need. The join solution looks fine to me, but maybe you

should check whether you need an OUTER JOIN for one of the two joins.

What do you call master?

> the problem here is that for each entry in VBRK it displays all cust. from master

> again and again every time

this is not clear, probably some people understand, I don't, use the names of the tables and

not what they mean to you!

Siegfried

[Former Member]

k