Please modify my select statement...

[aris_hidalgo]

Hello experts,

I am having an error in my select statement. What I want to do is restrict it where its bwart is equal to Z11 or bwart in 601, 631 and lgort starts with 5.Below is the code. Thank you guys!

SELECT mblnr

mjahr

matnr

ebeln

ebelp

menge

bwart

lgort

FROM mseg

INTO TABLE it_mseg

FOR ALL ENTRIES IN it_mkpf

WHERE mblnr = it_mkpf-mblnr

AND mjahr = it_mkpf-mjahr

AND werks = p_werks

AND parent_id = '000000'

AND ( bwart = 'Z11' OR ( bwart in ( '601' , '631' )

AND lgort LIKE '5%' ).

[Former Member]

SELECT mblnr

mjahr

matnr

ebeln

ebelp

menge

bwart

lgort

FROM mseg

INTO TABLE it_mseg

FOR ALL ENTRIES IN it_mkpf

WHERE mblnr = it_mkpf-mblnr

AND mjahr = it_mkpf-mjahr

AND werks = p_werks

AND parent_id = '000000'

AND ( bwart = 'Z11' OR bwart in ( '601' , '631' ))

AND lgort LIKE '5%' .

[Former Member]

ranges: r_bwart for mseg-bwart.
r_bwart-low = '601'.
r_bwart-option = 'EQ'.
r_bwart-sign = 'I'.
append r_bwart.

r_bwart-low = '631'.
r_bwart-option = 'EQ'.
r_bwart-sign = 'I'.
append r_bwart.

SELECT mblnr
mjahr
matnr
ebeln
ebelp
menge
bwart
lgort
FROM mseg
INTO TABLE it_mseg
FOR ALL ENTRIES IN it_mkpf
WHERE mblnr = it_mkpf-mblnr
AND mjahr = it_mkpf-mjahr
AND werks = p_werks
AND parent_id = '000000'
AND ( bwart = 'Z11' OR 
      bwart in r_bwart )
AND lgort LIKE '5%'.

[aris_hidalgo]

Hi guys,

The condition lgort LIKE '5%' must only be used if bwart is found in r_bwart. How do we do that? Thanks!

[Former Member]

In that case you cannot have it as a part of the SELECT, but check for the value after the select by looping at the internal table.

Regards,

Ravi

[Former Member]

sorry.

[Former Member]

.....
<b>AND  bwart = 'Z11' 
OR  ( bwart in r_bwart
AND lgort LIKE '5%' )</b>.

Regards

Vijay

[Former Member]

ranges: r_bwart for mseg-bwart.
r_bwart-low = '601'.
r_bwart-option = 'EQ'.
r_bwart-sign = 'I'.
append r_bwart.
 
r_bwart-low = '631'.
r_bwart-option = 'EQ'.
r_bwart-sign = 'I'.
append r_bwart.
 
SELECT mblnr
mjahr
matnr
ebeln
ebelp
menge
bwart
lgort
FROM mseg
INTO TABLE it_mseg
FOR ALL ENTRIES IN it_mkpf
WHERE mblnr = it_mkpf-mblnr
AND mjahr = it_mkpf-mjahr
AND werks = p_werks
AND parent_id = '000000'.

" after selecting the data.., use delete statement.

delete it_mseg where 
  not bwart = 'Z11' OR 
  not  ( bwart in r_bwart 
AND not lgort cp '5%' ).

Regards

vijay

[aris_hidalgo]

Hi Vijay,

all records in my itab(it_mseg) was deleted. How come this happened? Thanks!

[Former Member]

did you check those values in debug mode?

it_mseg hold any values bwart = 601, 631 or Z11.

if there are no records definitely all of them will be deleted.

<b>delete it_mseg where
   bwart <> 'Z11' OR
    ( bwart not in r_bwart[]
AND not lgort cp '5%' ).</b>

Regards

vijay

[aris_hidalgo]

Yup, I did check those values and it contains z11, 601, 631.

I just want to remove those records which does not contain z11, 601, 631.

[Former Member]

delete it_mseg where
   bwart <> 'Z11' .

delete it_smeg where bwart not in r_bwart[].
delete it_mseg where not lgort cp '5%' .

try to split it and see...

Regards

vijay

[Former Member]

Hi,

  SELECT mblnr
         mjahr
         matnr
         ebeln
         ebelp
         menge
         bwart
         lgort
         FROM mseg
         INTO TABLE it_mseg
         FOR ALL ENTRIES IN it_mkpf
         WHERE mblnr     = it_mkpf-mblnr
           AND mjahr     = it_mkpf-mjahr
           AND werks     = p_werks
           AND parent_id = '000000'
           AND ( bwart = 'Z11' OR  bwart IN ('601' , '631') AND lgort LIKE '5%' ).

Regards,

Arun.

Message was edited by: Arun Sambargi

[Former Member]

Try:

SELECT mblnr

mjahr

matnr

ebeln

ebelp

menge

bwart

lgort

FROM mseg

INTO TABLE it_mseg

FOR ALL ENTRIES IN it_mkpf

WHERE mblnr = it_mkpf-mblnr

AND mjahr = it_mkpf-mjahr

AND werks = p_werks

AND parent_id = '000000'

AND ( bwart = 'Z11' OR ( bwart in ( '601' , '631' )

AND lgort LIKE '5%' )<b>)</b>.

[aris_hidalgo]

Hi guys,

I am presently debugging the report but the select statement takes a loooong time and still haven't finished yet. Is there a way to speed this up?

Again, thank you experts!

[Former Member]

hi VIray,

Use <b>Indexes</b> in the select statement. because MSEG table consists of huge data so it takes lots of processing time.

Regards,

Santosh