Skip to Content
-2

how to fetch the data using select and delete statement?

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Mar 12 at 11:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 12 at 12:11 PM
    • 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.
    Add comment
    10|10000 characters needed characters exceeded

  • Mar 12 at 06:43 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 12 at 07:21 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 12 at 04:07 PM

    "how to merge DELETE statement with SELECT statement"

    The general answer to that general question is

    DELETE WHERE IN ( subquery ).

    Add comment
    10|10000 characters needed characters exceeded