>Hi all,
>
>Follow-up from last Thursday's thread# 1338981
>
>this is one scan endscan that I have in my program ( I inherited it, working on cleaning it up. :D)
>

>SELECT srvceid, COUNT(DATE) AS wrksrvceDate , ALLT(STR(MONTH(DATE)))+ALLT(STR(YEAR(DATE))), ;
>  C1, C4 ;
>  FROM cost, cstcoef ;
>  WHERE cost.costcoef == cstcoef.coefid AND ;
>  (RIGHT("00"+ALLT(STR(MONTH(DATE))),2)+ALLT(STR(YEAR(DATE)))=ALLTR(sPeriod) ;
>  AND NOT EMPTY(dateappr) AND ALLT(STATUS)=='' AND NOT schoolclose AND NOT Cancelled) ;
>  GROUP BY srvceid, C1, C4 ;
>  INTO CURSOR c7
>
>SELECT (sCur)
>GO TOP
>SCAN
>  SELECT (sCur)
>  UPDATE BL731 SET BL731.wrksrvceDate=c7.wrksrvceDate ;
>    WHERE srvceid=c7.srvceid AND C1 = c7.C1 AND C4 = c7.C4
>ENDSCAN
>USE IN c7
>

>
>Rich suggested that I use index and replace all. My problem with indexes is that srvceid is a character field and c1 and c4 are numeric. How would I make a index for a character and numerical index without causing problems for the data?
>
>TIA
>Beth

Several problems here:
1. You use this as a field in result set:
ALLT(STR(MONTH(DATE)))+ALLT(STR(YEAR(DATE))
What if the first month of that result set is 1? then the whole field length will be 5 and at the end you can end with 12200 (Dec 200X). PAD it:
PADL(ALLT(STR(MONTH(DATE)))+ALLT(STR(YEAR(DATE)),6,[0])

2. use INNER JOIN instead of old WHERE. That makes code more readable:
3. Do not use ALLTRIM() or EMPTY() in WHERE clause because there are not optimizable.
4. I wonder HOW GROUP BY works if you didn't include in it the 3th selected field, but I suspect you have SET ENGINEBEHAVIUOR 70.
Don't use this, you will get unexpected results from your queries.


So lets go to SCAN ... ENDSCAN part:
You never change the record pointer of C7 cursor so you ALWAYS update sCur with the same values over and over agein (depends how many records sCur has)