topic Re: select statement problem in Application Development and Automation Discussions

select statement problem

Former Member — Fri, 06 Feb 2009 08:30:49 GMT

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....

Re: select statement problem

Former Member — Fri, 06 Feb 2009 08:37:10 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 08:37:12 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 08:39:04 GMT


> 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..???

Re: select statement problem

Former Member — Fri, 06 Feb 2009 08:43:56 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 08:44:35 GMT

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.

Re: select statement problem

former_member404244 — Fri, 06 Feb 2009 08:44:53 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 08:50:33 GMT

Hi,

In last select you can avoid name1 .

Regards

Pinaki

Re: select statement problem

Former Member — Fri, 06 Feb 2009 08:52:38 GMT

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

Re: select statement problem

SuhaSaha — Fri, 06 Feb 2009 08:59:15 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 09:00:26 GMT

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.....

Re: select statement problem

Former Member — Fri, 06 Feb 2009 09:01:57 GMT


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

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 09:11:32 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 09:15:28 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 09:23:51 GMT


> 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......

Re: select statement problem

Former Member — Fri, 06 Feb 2009 09:24:46 GMT

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

thanxx...

Re: select statement problem

Former Member — Fri, 06 Feb 2009 09:35:56 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 09:43:20 GMT

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

Re: select statement problem

Former Member — Fri, 06 Feb 2009 10:04:43 GMT


> 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....

Re: select statement problem

Former Member — Fri, 06 Feb 2009 10:05:33 GMT


> 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