topic Re: DELETE Statement in Application Development and Automation Discussions

DELETE Statement

Former Member — Wed, 08 Apr 2009 08:52:16 GMT

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

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 08:55:56 GMT

Hello Boddy,

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

Regards,

Anil.D

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 08:58:30 GMT

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.

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 09:00:15 GMT

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.

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 09:02:26 GMT

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.

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 09:03:47 GMT

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

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 09:04:54 GMT

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.

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 09:18:17 GMT

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

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 10:28:28 GMT

Hi,

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

Regards,

Sankar

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 10:49:40 GMT

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?

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 11:14:54 GMT

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

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 12:00:12 GMT

Hi.

Try CS or NS...

CS is for contains string..

and

NS is for does not contain string..

Re: DELETE Statement

Former Member — Wed, 08 Apr 2009 12:11:10 GMT

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