Russell;

Sometimes it helps to create separate SELECT statements and then work with the resultant cursors. You design them to give the correct dataset as you drill down. It can speed up some queries by a large margin. And I agree with Tore – goodbye to those ALLTRIMS!

Just try one thing at a time and check the timing as you go with a wait window , etc.

Tom


>Can someone please provide a suggestion for a better way to write the following sql statement:
>
>

>SELECT oInv.cDivname AS division,dStart as Start,dEnd AS
>     end,a.contno,VAL(a.contno) AS Contract,;
>     (a.qty * (a.len_ft + a.len_in/12)) AS tlen, ;
>     IIF(PADL(a.desc,4)=='  PL' OR PADL(a.desc,4)=='  CP',0,a.dim2) AS di2,;
>     IIF(PADL(a.desc,4)=='  PL' OR PADL(a.desc,4)=='  CP',0,a.dim3) AS di3,;
>     a.desc,a.dim1,a.dim2,a.dim3,a.misjunk,a.mtype,a.usedate,a.tojob,;
>     a.qty,a.len_ft,a.len_in,a.grade,a.totalwt,a.propage,a.proline,;
>     a.cutfrom,a.porder,a.popage,a.poline,ALLTRIM(b.vendorname) + '
>     ('+ALLTRIM(a.vendor)+')' AS vendor,a.heatno,a.value ;
>FROM usedmaterial a ;
>     LEFT JOIN source b;
>         ON ALLTRIM(a.vendor)== ALLTRIM(b.id) ;
>INTO CURSOR curTemp1 WHERE !DELETED() AND BETWEEN(a.adddate,dStart,dEnd) ;
>    AND PADL(a.grade,10) IN (SELECT PADL(selections,10) FROM curSpecsMover)
>    AND ALLTRIM(shapeconv(a.desc,a.dim1,a.dim2,a.dim3,a.misjunk)) IN (SELECT
>    ALLTRIM(selections) FROM curSectionMover) AND ;
>    PADL(a.contno,10) IN (SELECT PADL(selections,10) FROM curContractMover)
>
>

>
>This one is very slow!
>
>Thanks for the help!