topic Query problem. in Application Development and Automation Discussions

Query problem.

Former Member — Wed, 28 May 2008 19:20:02 GMT

Question1): How to optimize this query.

SELECT ZBRNM PRODH SUM( ZBIL1 ) SUM( ZSTD1 ) INTO TABLE IT_BRAND

FROM CF951

WHERE

YEARB = LV_PREYEAR AND PERDE = LV_PERDE AND VERSIO = '001' AND PLACTI

='0'

GROUP BY ZBRNM PRODH

ORDER BY ZBRNM.

LOOP AT IT_BRAND.

LV_PREMARGIN = ( IT_BRAND-SUMBIL1 ) - ( IT_BRAND-SUMSTD1 ).

LV_PREPERMARGIN = ( LV_PREMARGIN / IT_CURRBRAND-SUMBIL1 ) * 100.

WRITE: / IT_BRAND-ZBRNM, IT_BRAND-PRODH, IT_BRAND-SUMBIL1,

IT_BRAND-SUMSTD1,

LV_PREMARGIN.

ENDLOOP.

Question 2) In given query perde is fiscal period and i want to run this query from different fiscal period ( i means to say for 11 times for a year). how to give the range in where clasue.

If my question is not clear let me know i will give more details.

Re: Query problem.

Former Member — Wed, 28 May 2008 21:15:27 GMT

1) where exactly do you want to improve performance? the Select statement? the loop?

Sometimes, doing too much in your select statement will really clog your performance. i'd try taking out the SUMs and GROUPINGs... do that separately.

You can always create a test program with your original Select statement & compare the execution time of it (and only it).

Your loop can be optimized by looping at your itab into a work area, or even using field symbols, instead of using an itab with header line.

2) you want to run for all 12 fiscal periods at once? or one at a time? depending on how your variable lv_perde is declared... i would make it part of the screen selection with a select-options statement:

select-options: lv_perde for cf951-perde.

in your select statement you use:

WHERE perde IN lv_perde

Re: Query problem.

kesavadas_thekkillath — Thu, 29 May 2008 03:36:01 GMT

Instead of order by use

SELECT ZBRNM PRODH SUM( ZBIL1 ) SUM( ZSTD1 ) INTO TABLE IT_BRAND

FROM CF951

WHERE

YEARB = LV_PREYEAR AND PERDE = LV_PERDE AND VERSIO = '001' AND PLACTI

='0'

GROUP BY ZBRNM PRODH.

sort IT_BRAND by ZBRNM ascending.

declare a rnages variable...pass all the values into it

and in where clause use YEARB in r_YEAR