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

Select Statement Query

Former Member — Fri, 13 Apr 2007 18:55:46 GMT

hello experts,

here is the select statemenet can anyone modify this select statement in such a way that performance should be good....

SELECT-OPTIONS: s_ekgrp FOR ekko-ekgrp.

SELECT-OPTIONS: s_matkl FOR ekpo-matkl.

SELECT a_{ebeln a}lifnr a~ernam

b_{ebelp b}matnr b_{txz01 b}werks b_{menge b}meins b~ktpnr

b~konnr

c~eindt

d_{netpr d}infnr d~netpr

e~name1 into table T_EKKO from ekko as a

inner join eket as b on a_{ebeln = b}ebeln

inner join eket as c on b_{ebeln = c}ebeln and b_{ebelp = c}ebelp

inner join eine as d on a_{ekorg = d}ekorg

inner join lfa1 as e on a_{lifnr = e}lifnr

where a_{ekgrp in s_ekgrp and b}matkl in s_maktl.

so here my question is in the selection screen i am having only two field those r not key fileds in the corresponding tables where as in indexes those two are included as in ekko as ekko 1 as ekorg, and ekpo as ekpo 1 as matkl

how to include these indexes into the above select statement... plz its bit urgent...

Re: Select Statement Query

RichHeilman — Fri, 13 Apr 2007 18:58:35 GMT

The determinatino of using indexes on tables is totally up to the optimizer. Yes, I am aware that Hints can be used, but I am under the impression that it doesn't help unless you are running an Oracle database(i could be wrong).

Regards,

RIch Heilman

Re: Select Statement Query

Former Member — Fri, 13 Apr 2007 19:07:58 GMT

Hi,

You can use the following statement in your select statement:

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

Example:

SELECT carrid connid cityfrom

FROM spfli INTO (xcarrid, xconnid, xcityfrom)

WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

Thanks,

Srinivasa

Message was edited by:

Srinivasa

Re: Select Statement Query

RichHeilman — Fri, 13 Apr 2007 19:15:22 GMT

Also, there seems to be mistakes in your SELECT, notice you are joining EKET twice, this is not right, and the join to EINE is not right either.

Here is some modified code.



REPORT  zrich_0001.

TABLES: ekko, ekpo.

data: begin of t_ekko occurs 0,
      ebeln type ekko-ebeln,
      lifnr type ekko-lifnr,
      ernam type ekko-ernam,
      ebelp type ekpo-ebelp,
      matnr type ekpo-matnr,
      txz01 type ekpo-txz01,
      werks type ekpo-werks,
      menge type ekpo-menge,
      meins type ekpo-meins,
      ktpnr type ekpo-ktpnr,
      konnr type ekpo-konnr,
      eindt type eket-eindt,
      netpr type eine-netpr,
      infnr type eine-infnr,
      name1 type lfa1-name1,
      end of t_ekko.

SELECT-OPTIONS: s_ekgrp FOR ekko-ekgrp.
SELECT-OPTIONS: s_matkl FOR ekpo-matkl.

SELECT a~ebeln a~lifnr a~ernam
b~ebelp b~matnr b~txz01 b~werks b~menge b~meins b~ktpnr
b~konnr
c~eindt
d~netpr d~infnr
e~name1
     INTO TABLE t_ekko FROM ekko AS a
           INNER JOIN ekpo AS b
                ON a~ebeln = b~ebeln
           INNER JOIN eket AS c
                ON b~ebeln = c~ebeln
               AND b~ebelp = c~ebelp
           INNER JOIN eine AS d
                ON b~INFNR = d~INFNR
           INNER JOIN lfa1 AS e
                ON a~lifnr = e~lifnr
                  WHERE a~ekgrp IN s_ekgrp
                    AND b~matkl IN s_matkl.

  check sy-subrc = 0.

This coding runs pretty fast in my system.

Regards,

Rich Heilman

Re: Select Statement Query

Former Member — Fri, 13 Apr 2007 19:15:42 GMT

I didn't understand srinivas

then how to optimise the performance of above select statement is there any other way

one more thing also there like...i have filter the po number by putting the below condition in the above select statement

if <b>ekpo~elikz = ' '</b> , that means when i put this condition then the po r open.. how to add this condition to the above select statement...

plz someone help me its bit urgent....

siri

Re: Select Statement Query

RichHeilman — Fri, 13 Apr 2007 19:17:59 GMT

So adding the additional filter to the WHERE clause would be like so.

SELECT a~ebeln a~lifnr a~ernam
b~ebelp b~matnr b~txz01 b~werks b~menge b~meins b~ktpnr
b~konnr
c~eindt
d~netpr d~infnr
e~name1
     INTO TABLE t_ekko FROM ekko AS a
           INNER JOIN ekpo AS b
                ON a~ebeln = b~ebeln
           INNER JOIN eket AS c
                ON b~ebeln = c~ebeln
               AND b~ebelp = c~ebelp
           INNER JOIN eine AS d
                ON b~INFNR = d~INFNR
           INNER JOIN lfa1 AS e
                ON a~lifnr = e~lifnr
                  WHERE a~ekgrp IN s_ekgrp
                    AND b~matkl IN s_matkl
                    and b~elikz = space.  "<- Right HEre

Regards,

Rich Heilman

Message was edited by:

Rich Heilman

Re: Select Statement Query

Former Member — Fri, 13 Apr 2007 20:26:25 GMT

Rich

this select statement is taking much longer time in the production system

can you tell me any alternative way to do this

Re: Select Statement Query

RichHeilman — Fri, 13 Apr 2007 20:36:06 GMT

The SELECT statement that I've provided is taking too long? In my system it is running good.

Regards,

Rich Heilman

Re: Select Statement Query

Former Member — Fri, 13 Apr 2007 20:50:55 GMT

I checked in the production system rich... It is having lot of records...so it is taking so much of time may be

is there any alternate option to change this select statement so that i can optimise the performance in production system also

Re: Select Statement Query

Former Member — Fri, 13 Apr 2007 21:07:24 GMT

Hi,

Please check using secondary indexes and include the following statement

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")' in your select query and try.

Thanks,

Srinivasa

Re: Select Statement Query

Former Member — Fri, 13 Apr 2007 21:21:26 GMT

srinivasa

in indexes 1 those fiels r not in the first position

those r in the 4th position in the both the tables so even though i add it, its not useful i think rite