If the expression is "fully optimized", it looks like you have an index on alltrim(upper(ino)), etc. This might be a problem; an index expression can not have a variable length! For full optimization, try changing to some expression that has a fixed length, like a space-padded expression. See padr().
If the fields have trailing spaces, you don't need alltrim() for the filter: upper(bcode) = "AW" will return all records that
begin with "AW" (but please check: SET EXACT).
Also, check how many records are returned for each individual part of the expression: upper(ino), upper(bcode), upper(loccode). If one of these expressions returns too many records (say, more than a thousand),
delete the corresponding index.
Hilmar.
>Hi,
> my SQL
>
>
>SELECT Ino, bCode ;
>FROM Stock_Status ;
>WHERE ALLTRIM(UPPER(INO)) = "012" AND ALLTRIM(UPPER(BCODE)) = "AW" AND ;
> ALLTRIM(UPPER(LOCCODE)) == "KL" ;
>GROUP BY INO,BCODE ;
>INTO CURSOR csrResult
>
>
>
>SYS(3054) show this query run with full optimized.
>
>Thnak you
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)