topic Re: SELECT QUERY in Application Development and Automation Discussions

SELECT QUERY

Former Member — Thu, 11 Jan 2007 14:54:39 GMT

This select query

sort itab by lifnr matnr ekorg werks esokz

SELECT lifnr matnr ekorg esokz werks datbi datab knumh

FROM a017

INTO CORRESPONDING FIELDS OF TABLE it_a017

FOR ALL ENTRIES IN itab

WHERE kschl = 'PB00' AND

lifnr = itab-lifnr AND

matnr = itab-matnr AND

ekorg = itab-ekorg AND

werks = itab-werks AND

esokz = itab-esokz AND

datbi GT sy-datum.

is taking a very very long time....the no. of records in itab are 70k and a017 has the equal number. I made sure itab is not empty before this statement is executed. Could someone help me with this...to improve performance....thanks.

Re: SELECT QUERY

Former Member — Thu, 11 Jan 2007 15:01:34 GMT

one thing is if possible remove CORRESPONDING FIELDS OF

Re: SELECT QUERY

Former Member — Thu, 11 Jan 2007 15:03:16 GMT

You should add KAPPL to the WHERE clause. This will allow the SELECT statement to use the primary index.

Rob

Re: SELECT QUERY

Former Member — Thu, 11 Jan 2007 15:05:29 GMT

KAPPL is the highest field in the composite, primary key. Add that field.

IF you do not have "know" the value(s) of KAPPL at execution time, then consider building a secondary index on KSCHL and some lower level fields.

Re: SELECT QUERY

Former Member — Thu, 11 Jan 2007 15:05:49 GMT

Hi sandeep,

SELECT lifnr matnr ekorg esokz werks datbi datab knumh

FROM a017

INTO CORRESPONDING FIELDS OF TABLE it_a017

FOR ALL ENTRIES IN itab

WHERE <b>KAPPL = 'V'</b> and

kschl = 'PB00' AND

lifnr = itab-lifnr AND

matnr = itab-matnr AND

ekorg = itab-ekorg AND

werks = itab-werks AND

esokz = itab-esokz AND

datbi GT sy-datum and

<b>datab lt sy-datum</b>.

try this.

Rrds

KER

Re: SELECT QUERY

Former Member — Thu, 11 Jan 2007 15:08:15 GMT

1. declared an internal table which will be having all the fields which you need.

by looking to your code i can say

data: begin of output occurs 0,

lifnr like a017-lifnr

matnr like a017-matnr,

ekorg like a017-ekorg,

esokz like a017-esokz,

werks like a017-werks,

datbi like a017-databi,

datab like a017-datab,

knumh like a017-knumh,

end of output.

sort itab by lifnr matnr ekorg werks esokz

*donot use corresponding fields.

select lifnr matnr ekorg esokz werks datbi datab knumh

FROM a017

INTO TABLE output

FOR ALL ENTRIES IN itab

WHERE kschl = 'PB00' AND

lifnr = itab-lifnr AND

matnr = itab-matnr AND

ekorg = itab-ekorg AND

werks = itab-werks AND

esokz = itab-esokz AND

datbi GT sy-datum.

Re: SELECT QUERY

Former Member — Wed, 14 Feb 2007 02:50:30 GMT

The issue is resolved...thanks very much guys...i really appreciate it...

Re: SELECT QUERY

Former Member — Wed, 14 Feb 2007 02:50:33 GMT

The issue is resolved...thanks very much guys...i really appreciate it...