how to fetch the data using select and delete statement?

[achin_tomer]

Hi All,

I am having an issue with the select query, i m creating an SD Report in which i m fetching data from 4 tables ( VBAK,VBPA,VBKD,KONV).

VBAK Fields are vbeln,auart,vkorg,vtweg,spart,vgrp,ernam,audat, knumv.

VBPA Fields are parvw , kunnr. (Pass vbak- vbeln)

VBKD Fields are bskd ,bstdk.(Pass vbak-vbeln)

KONV Fields are kschl ,knumv, kwert, kawrt. ( Pass vbak-knumv)

I am able to fetch the data successfully from VBAK VBPA VBKD but Facing problem to fetch data from KONV.

Conditions to fetch the data from KONV are:-

knumv in vbak-knumv.

and KSCHL should be equal to 'JOIG' or 'JOCG' or 'JOSG'.

If above conditions are satisfied than should pick kawrt and kewrt from KONV.

I am looking for the query using delete statement beacause when i am defining all the conditions in single select statement , it is taking a lot of time to fetch the data.So,I m trying to do it in this way:-

<<<<SELECT kschl knumv kwert kawrt FROM KONV INTO TABLE LT_KONV FOR ALL ENTRIES IN LT_VBAK

WHERE KNUMV = LT_VBAK-KNUMV.

DELETE LT_VBAK where kschl NE 'JOIG' OR 'JOCG' OR 'JOSG'.>>>>

but i m not getting the data.

Can anyone please suggest me any better way or modifications i can do in it!!

Thank you in advance.

[achin_tomer]

Hi All,

I am having an issue with the select query, i m creating an SD Report in which i m fetching data from 4 tables ( VBAK,VBPA,VBKD,KONV).

VBAK Fields are vbeln,auart,vkorg,vtweg,spart,vgrp,ernam,audat, knumv.

VBPA Fields are parvw , kunnr. (Pass vbak- vbeln)

VBKD Fields are bskd ,bstdk.(Pass vbak-vbeln)

KONV Fields are kschl ,knumv, kwert, kawrt. ( Pass vbak-knumv)

I am able to fetch the data successfully from VBAK VBPA VBKD but Facing problem to fetch data from KONV.

Conditions to fetch the data from KONV are:-

knumv in vbak-knumv.

and KSCHL should be equal to 'JOIG' or 'JOCG' or 'JOSG'.

If above conditions are satisfied than should pick kawrt and kewrt from KONV.

I am looking for the query using delete statement beacause when i am defining all the conditions in single select statement , it is taking a lot of time to fetch the data.So,I m trying to do it in this way:-

<<<<SELECT kschl knumv kwert kawrt FROM KONV INTO TABLE LT_KONV FOR ALL ENTRIES IN LT_VBAK

WHERE KNUMV = LT_VBAK-KNUMV.

DELETE LT_VBAK where kschl NE 'JOIG' OR 'JOCG' OR 'JOSG'.>>>>

but i m not getting the data.

Can anyone please suggest me any better way or modifications i can do in it!!

Thank you in advance.

[former_member302126]

Hi Achin,

Main question is: are you not getting any data from DB (so from your SELECT statement) or after manipulation of internal table using DELETE statement?

If the latter, then at first I would suggest to merge your DELETE condition into SELECT statement:

IF lt_vbak IS NOT INITIAL.
SELECT kschl knumv kwert kawrt 
FROM konv
INTO TABLE lt_konv
FOR ALL ENTRIES IN lt_vbak
WHERE knumv = lt_vbak-knumv
  AND (   kschl EQ 'JOIG' 
       OR kschl EQ 'JOCG' 
       OR kschl EQ 'JOSG' ).
ENDIF.
"you can also use "IN" operator: ...AND kschl IN ('JOIG','JOCG','JOSG').

If above won't work - then it means you've got some logical issue in your algorithm and data is not in DB (according to you logic).

Hope that helps,

Best regards,

Marcin

[achin_tomer]

Hi Marcin,

Thanks for your reply,

I m getting the data by using SELECT statement but it is disturbing the performance ( like taking 5-10 mins for fetching 50 records).

so i m trying to do it using DELETE statement. I m getting the data when using DELETE statement when i m defining only one condition.

Like this, but not when i am applying all the conditions.

SELECT kschl knumv kwert kawrt FROM KONV INTOTABLE LT_KONV FORALLENTRIESIN LT_VBAK
WHERE KNUMV = LT_VBAK-KNUMV.DELETE LT_VBAK where kschl NE'JOIG'.

can you please suggest me how to merge DELETE statement with SELECT statement.

Thank you once again!!!!

[former_member302126]

I see, than you should go with:

DELETE lt_vbak WHERE kschl NE 'JOIG'
                 AND kschl NE 'JOCG'
                 AND kschl NE 'JOSG'.

[achin_tomer]

Hi Marcin,

With your help, my issue is got resolved now .

Thank you.

Achin.

[RaymondGiuseppi]

• is KONV a cluster table in your system, was that the actual reason not to use a JOIN, and not a performance problem?
• What is your actual syntax for delete, not the one you posted? I can only suspect some DELETE WHERE kschl NE 'JOIG' OR kschk NE 'JOCG', etc. which is always true, use AND and not OR.

[retired_member]

"how to merge DELETE statement with SELECT statement"

The general answer to that general question is

DELETE WHERE IN ( subquery ).

[Jelena_Perfiljeva]

I'm surprised no one mentioned this concern yet: why are you just deleting the data directly from KONV table? There could also be data in KONM table (scales) and what's not. The direct updates of standard SAP tables by the customer normally should not be needed.

[former_member302126]

Jelena, Horst,
Actually the question asked by Achin is misleading - he doesn't want to delete DB data, he is just combining SQL SELECT statement with internal table DELETE operation due to performance reasons.
That means DELETE applies only to the internal table data fetched earlier. He chose this way because using the same condition in SQL leads to longer running time.