DELETE Statement

[Former Member]

Hi Experts

I have the below Query:

 SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG
A~ERDAT A~ERZET A~PSTYV D~AUART E~ETTYP E~EDATU
INTO TABLE INT_RES
FROM VBAK AS D INNER JOIN VBAP AS A 
ON D~VBELN EQ A~VBELN
INNER JOIN VBEP AS E
ON E~VBELN EQ A~VBELN AND E~POSNR EQ A~POSNR
WHERE D~VBELN IN S_VBELN AND
D~AUART IN S_AUART AND
D~VKORG IN S_VKORG AND
D~VBTYP EQ 'C' AND

( ( A~MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) OR
( MATNR LIKE C_PREFIX_FP AND KWMENG NE A~KBMENG ) ) AND

A~ABGRU EQ SPACE AND
A~MTVFP IN R_MTVFP AND
A~PRCTR IN R_PRCT AND
E~ETENR EQ '1'.

In this, I want to separate the below portion of WHERE clause using DELETE statement:

( ( A~MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) OR
( MATNR LIKE C_PREFIX_FP AND KWMENG NE A~KBMENG ) ) AND

How can I write the DELETE statement? Plz suggest.

Thx

BD

[Former Member]

Hello Boddy,

Can you please know me what is ur exact requirement,why you want to use delete statement.

Regards,

Anil.D

[Former Member]

Hi Anil

This is for Performance Improement of this query. I think usage of these equality/inequality operators in below lines of code may access all the records of the tables and will take too much of time.

( ( A~MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) OR
( MATNR LIKE C_PREFIX_FP AND KWMENG NE A~KBMENG ) ) AND

Plz suggest.

[Former Member]

if it is rel. to performance improvement, make above delete statements as negative

[Former Member]

Delete from INT_RES where (MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) OR ( MATNR LIKE C_PREFIX_FP AND KWMENG NE KBMENG )

i am assuming that, after the retrieving the data using the select statement, u want to delete the entries which are matching with the above conditions. if i am right, you can check above delete statement.

[Former Member]

Hi Lakshmi

I think you are wrong.

( ( A~MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) OR
( MATNR LIKE C_PREFIX_FP AND KWMENG NE A~KBMENG ) ) AND

This code is part of main query and it will fetch records which satisfy the said conditions. When we are taking it out and use in DELETE statement, dont we want to reverse it? Also, does DELETE statement accept multiple checking conditions?

Anybody correct me, if I am wrong.

[Former Member]

Hi,

With the Delete statement is complecated to instead you loop through the internal table and delete the records which are not in the given criteria.

SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG
A~ERDAT A~ERZET A~PSTYV D~AUART E~ETTYP E~EDATU D~ZZ_MSPOSNR  D~KBMENG
D~KWMENG
INTO TABLE INT_RES
FROM VBAK AS D INNER JOIN VBAP AS A 
ON D~VBELN EQ A~VBELN
INNER JOIN VBEP AS E
ON E~VBELN EQ A~VBELN AND E~POSNR EQ A~POSNR
WHERE D~VBELN IN S_VBELN AND
D~AUART IN S_AUART AND
D~VKORG IN S_VKORG AND
D~VBTYP EQ 'C' AND
A~ABGRU EQ SPACE AND
A~MTVFP IN R_MTVFP AND
A~PRCTR IN R_PRCT AND
E~ETENR EQ '1'.

LOOP AT INT_RES
IF ( INT_RES-AMATNR LIKE C_PREFIX_SP AND 
      INT_RES-DZZ_MSPOSNR NE 0 AND 
      INT_RES-DKBMENG EQ 0 ) OR 
    ( INT_RES-DMATNR LIKE C_PREFIX_FP).
ELSE.
DELETE INT_RES INDEX SY_TABIX.
ENDID.

ENDLOOP.

[Former Member]

Hi Avinash

Great to see among Top Scorers! I tried this logic. It seems the relational operator "LIKE" is not supported. What can be used instead?

Also, in the statement, DELETE INT_COLL_ORD INDEX SY_TABIX, we have used the system variabe SY-TABIX. This also is shown as error - saying it is neither in one of the specified tables nor defined by a DATA statement.

Plz suggest....

[Former Member]

Hi,

Instead of LIKE you can try something like 'CP' matches pattern. That is not SY_TABIX, it is SY-TABIX.

Regards,

Sankar

[Former Member]

Hi Avinash Kodarapu and Shankar Muvva

Performance-wise, the changes you have proposed is very good.

But, the usage of "CP" like pattern in place of LIKE gives me wrong results.

Could you plz help me out in this?

[Former Member]

Hi

Performance-wise, the above changes proposed are very good.

But, the usage of "CP" like pattern in place of LIKE gives me wrong results.

It seems the relational operator "LIKE" is not supported in relational comparison. What can be used instead?

Could anybody plz help me out in this?

Thx

BD

[Former Member]

Hi.

Try CS or NS...

CS is for contains string..

and

NS is for does not contain string..

[Former Member]

Hi...

When I use CS, it gives 0 records extracted; whereas I must get atleast 10 records. So, usage of CS is not apt here.

Instead, I have used the pattern value directly in the expression and now it is working fine.

Thx