topic Re: ABAP Select Query in Application Development and Automation Discussions

ABAP Select Query

former_member671009 — Thu, 19 Aug 2010 10:19:25 GMT

Hi Experts,

I am running one report program where the user gives only the date in the selection screen. I need to select all the document number which has not been sent before and equal to that date and send as file to another system. I have one standard table and custom ztable. I will be updating the document number,store number and posting date of the document in the ztable once the file is sent.

I need to put a query where i need to compare the date as less than equal to selection screen date and I should select only the document numbers that is not existing in my ztable.

How to proceed on this query??

Regards

Sridevi S

Re: ABAP Select Query

Former Member — Thu, 19 Aug 2010 10:31:00 GMT

Hi,

Highlevel code:

Select from Standard Table where date<=p_date into table tbl1

Select from "Z" table where document number = tbl1-docno.

into table tbl2

loop on tbl2

read table tbl1 where tbl1-docno = tbl2-docno

if found

delete record in tbl1

endif.

endloop

TBL1 is your final table which need to transferred.

Dont think of single query for the same. If by any chance you are able to create such query it will be very poor performing.

Edited by: Arun Singhal on Aug 19, 2010 12:33 PM

Re: ABAP Select Query

Former Member — Thu, 19 Aug 2010 10:42:50 GMT

Hi Sridevi,

Please refer the below code snippet.



SELECT DOC_NR 
       FIELDY 
       FIELDZ 
       FROM STANDARD TABLE INTO 
       TABLE ITAB WHERE DATE IN S_DATE 

SELECT DOC_NR 
       FROM ZTABLE INTO TABLE ITAB1 
       FOR ALL ENTRIES IN ITAB-DOC_NR
       WHERE DOC_NR = ITAB_DOC_NR.


LOOP AT ITAB1.
READ TABLE ITAB WITH KEY DOC_NR = ITAB1-DOC_NR.

IF SY-SUBRC = 0.
DELETE TABLE ITAB FROM ITAB.
ENDIF.

ENDLOOP.

Close the thread if this solves your query.

Regards

Abhii

Re: ABAP Select Query

Former Member — Thu, 19 Aug 2010 10:49:16 GMT

Hi,

You can follow below code:

Select * from standard table into I_itab where date LE P_date.

Select * from custome table for all entries in i_itab into i_itab1 where document number = i_itab-document number.

Loop at i_itab into wa_itab.
   v_index = sy-tabix.
   
   read table i_itab1 into wa_itab1 where document number = wa_itab-document number.
   if sy-subrc EQ 0.
      delete i_itab index v_index.
   endif.
clear: wa_itab1, wa_itab, sy-tabix.
endloop.

Thanks,

Archana

Edited by: Archana Pawar on Aug 19, 2010 12:51 PM

Re: ABAP Select Query

former_member671009 — Thu, 19 Aug 2010 11:02:28 GMT

Hi all,

I am doing like what you people have mentioned only. But since my standard table has around 6 lakh entries and when i use less than condition date it is taking around 5 mins to execute that query.

Is there any way to resolve this performance issue.

Is there any way to use not in select query for two tables. kindly let me know is there any way to use 'NOT IN' for tables in select query.

Regards

Sridevi S

Re: ABAP Select Query

Former Member — Thu, 19 Aug 2010 11:22:22 GMT

Hi,

Dont use NOT IN or negative statements in select queries. They are ver poor performing.

Try to identify secondary index keys with date field and if possible try using them in your query

If no index found try with other seconday index keys.

Delete records where date <= selection date

in loop put the above statement.

Cheers