topic Re: Problem with select query. in Application Development and Automation Discussions

Problem with select query.

Former Member — Thu, 21 May 2009 05:35:01 GMT

Hi Experts,

I need to modify a program which contains the below mentioned query.

SELECT * FROM yvw40 .

DELETE FROM yvw40 WHERE period IN s_month AND

fiscalyear IN s_year AND

comp_code IN compcode AND

acct_nbr IN gl_accts.

ENDSELECT.

YVW40 is a custom table for GL Extract.

The delete statement was not working properly hence i added COMMIT WORK statement after DELETE statement.

The problem is, after adding COMMIT WORK, i am getting dump sometimes and sometimes there is no dump.

Can somebody suggest the reason why this is happening and how to rectIfy the same.

Thanks in advance.

Regards

Balu

Re: Problem with select query.

former_member156446 — Thu, 21 May 2009 05:45:07 GMT

try to use commit after endselect.

Re: Problem with select query.

Former Member — Thu, 21 May 2009 05:55:39 GMT

Hi balu,

Before a database commit the system expects that all the database selections are complete.

So if we write a commit in between select and endselect the system throws a dump saying that the selection isnt yet complete and that there is an interruption in the select.

So do a commit. after the endselect statement .

Regards,

Vidya Chowdhary A.

Re: Problem with select query.

Former Member — Thu, 21 May 2009 06:29:02 GMT

Hi balu ,

As Vidya has said the the select statement should get completed before the commit . U can use the below logic .

Data: IT_YVW40 type standard table of YVW40.
REFRESH it_yvw40[].
SELECT  *  
    FROM yvw40  
    INTO TABLE            it_yvw40
    WHERE period         IN s_month AND
                 fiscalyear    IN s_year AND
                 comp_code  IN compcode AND
                 acct_nbr      IN gl_accts.. 
IF sy-subrc EQ 0.
    DELETE FROM yvw40 FROM TABLE it_yvw40.
    IF sy-subrc NE 0.
        MESSAGE 'Unable to delete the Records' TYPE 'E'.
    ENDIF
ENDIF

Hope this helps

Regards

Arun Thiyagarajan

Re: Problem with select query.

Former Member — Thu, 21 May 2009 06:29:09 GMT

Hi,

1. I am not able to understand why you have used Delete from YVW40 statment inside Select and End select . This will not satisfy ur need.

2. Instead you can directly use delete from YVW40, after delete just do a commit work

3. perfomance wise this will be much better

Re: Problem with select query.

Former Member — Thu, 21 May 2009 06:41:48 GMT

Thanks for ur responses.

I know the query is not appropriate.

But the program was written long back, may in 1999 i suppose.

The requirement was to change another Select query in the same program.

While testing i found the above mentioned problem, i am just trying to fix it.

Regards

Balu

Re: Problem with select query.

Former Member — Thu, 21 May 2009 09:26:36 GMT

Hi Balu,

In Debugging mode if you enter into SELECT ...... ENDSELECT sometimes it will go to dump. Try to write SELECT single or UPTO one row. Avoid SELECT ...... ENDSELECT. You can check it out by putting break point before and after the loop. it wont go to dump.

Regards,

Subrahmanyam.

Re: Problem with select query.

Former Member — Thu, 21 May 2009 10:02:12 GMT

Thanks for all the replies.

I placed the commit work outside the Select.... End Select and there is dump now.

Thanks

Balu

Re: Problem with select query.

Former Member — Thu, 21 May 2009 13:13:30 GMT

It may "work" now, but it makes no sense at all. You might want to rethink the process.

Rob

Re: Problem with select query.

Former Member — Fri, 22 May 2009 15:12:02 GMT


    DELETE   FROM yvw40  
                    WHERE period IN s_month AND
                    fiscalyear          IN s_year AND
                    comp_code        IN compcode AND
                    acct_nbr            IN gl_accts.. 
    IF sy-subrc NE 0.
        MESSAGE 'Unable to delete the Records' TYPE 'E'.
    ENDIF

this would also work,... but it dangerous, it can happen that it delete the whole table, if the

ranges are empty!

So it probably better to read the keys of the records which should be delete. But still your coding

can delete the whole table, just a bit slower than the coding above

what is the use of a refresh on a just created table?