topic Re: Please modify my select statement... in Application Development and Automation Discussions

Please modify my select statement...

aris_hidalgo — Thu, 08 Jun 2006 07:09:00 GMT

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%' ).

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 07:14:40 GMT

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

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 07:25:49 GMT

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

Re: Please modify my select statement...

aris_hidalgo — Thu, 08 Jun 2006 07:34:21 GMT

Hi guys,

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

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 07:40:47 GMT

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

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 07:42:04 GMT

sorry.

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 07:44:46 GMT

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

Regards

Vijay

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 07:48:54 GMT

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

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 07:54:35 GMT

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

Re: Please modify my select statement...

aris_hidalgo — Thu, 08 Jun 2006 07:56:46 GMT

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!

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 07:58:06 GMT

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

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 08:06:22 GMT

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

Re: Please modify my select statement...

aris_hidalgo — Thu, 08 Jun 2006 08:13:01 GMT

Hi Vijay,

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

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 08:21:46 GMT

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

Re: Please modify my select statement...

aris_hidalgo — Thu, 08 Jun 2006 08:25:41 GMT

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.

Re: Please modify my select statement...

Former Member — Thu, 08 Jun 2006 08:32:48 GMT

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