Dear List
(Sorry - Sent this earlier without a Subject)
Please see the code below.
I've been struggling with this for some time now (you may remember my
post of a week ago or so). I'm getting all the stops for a particulat
bus route. This is achieved with the 1st SQL. Bit disappointed in
that it takes c. 6-7 seconds but, erh, what can you do?
Next I need to display them as a layer. The SQL result was used OK, but
then I realised I needed to display each stop's running order too
Stops Table holds just Stop details
RZStop links Stops to Routes in a m-m reln.
For a given Route, RZStop keeps the "running order" against each stop
The "IN" clause of the SQL wouldn't allow me to pass up any extra data
from RZStop, i.e. the outer Select can't include any RZStop fields.
So after much wrangling I've been reduced to the intermediate cursor,
csrTemp, being used in the do loop to get the Running order and build up
the csrStops cursor.
Now it takes some 26-30 seconds which I think is excessive and
unacceptable.
Anybody got any ideas how I could do this MUCH more efficiently?
'ppreciate it
Terry McDonnell
________________________________
Sub SHOW_STOPS_TOO
'_________________
Dim lcRouteNo, lcGen, lcDirectn, lcStop_No, lcName, lcLoc1, lcLoc2,
lcCounty, lcDistrict as String,
lnZone, lnRowID, lnRunOrder as SmallInt, lnEastings, lnNorthings as
Integer, loObj as Object
lcRouteNo = RTRIM$( UCASE$( gcRouteNo))
lcGen = UCASE$( gcGen)
lcDirectn = UCASE$( gcDirectn)
Print "Please wait. Finding Stops for selected Route ..."
Select Stop_No, County, Name, Loc1, Loc2, Zone_No, Obj
from Stop
where Stop_No + County in
( Select Stop + County
from RZStop
where RTRIM$( RZStop.Rte_No) = lcRouteNo
and RZStop.Rte_Let = lcGen
and RZStop.Directn = lcDirectn
and RZStop.SeqNo = 1)
into csrTemp NoSelect ' Route's Stops only
Print "Please wait. Getting each Stop's Running Order..."
Fetch first from csrTemp
Do while not EOT( csrTemp)
lcStop_No = RTRIM$( csrTemp.Stop_No)
lcCounty = RTRIM$( csrTemp.County)
loObj = csrTemp.Obj
lcName = csrTemp.Name
lcLoc1 = csrTemp.Loc1
lcLoc2 = csrTemp.Loc2
lnZone = csrTemp.Zone_No
Select RunOrder from RZStop
Where RZStop.stop = lcStop_No
and RZStop.county = lcCounty
and RTRIM$( RZStop.Rte_No) = lcRouteNo
and RZStop.Rte_Let = lcGen
and RZStop.Directn = lcDirectn
and RZStop.SeqNo = 1
into csrX NoSelect
lnRunOrder = csrX.RunOrder
Insert into csrStops ( Stop_No, Name, Loc1, Loc2, Zone_No,
RunOrder, Obj)
Values ( lcStop_No, lcName, lcLoc1, lcLoc2, lnZone, lnRunOrder,
loObj)
Fetch next from csrTemp
Loop
Commit table csrStops
Print Chr$(12) ' Clear message box?
Close Window Message
Call SHOW_STOPS_HILITE
End Sub
