Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
I'd suggest you look at the SET KEY command over set filter in this case. You have an index expression and the order is set. You may find this gives much better performance. I know that doesn't address the real problem. In a relation, one is specifying an expression that results in a value that matches values of an index in the target table. Your relation expression results in .T. / .F. Your routine would have to return a value to match the index tag in specprice cPrSched+cPCode + STR({^3000/01/01}-EFFDATE)
>Hi
>
>I am developing an work order system. Each product/Service has a price based on the effective date (i.e. the price becomes effective on a certain date)
>
>I wrote the following function in the 'Stored Procedures' section for the database. WOTS.dbc
>
>PROCEDURE relCurrPrice
>LPARAMETERS cPrSched, cPcode
>*
>* find the current price in price schedule = cPrSched
>* for product/service = cPcode
>*
>LOCAL cSeekKey
>cSeekKey = cPrSched+cPcode+STR({^3000/01/01}-DATE())
>cSaveAlias=ALIAS()
>
>SELECT SPECPRICE
>SET ORDER TO 1 && PRSCHED+CODE+STR({^3000/01/01}-EFFDATE)
>cSaveFilter = FILTER()
>SET FILTER TO prsched=cPrsched AND code=cPcode
>
>lFound=INDEXSEEK(cSeekkey,.T.)
>IF !lFound
> SKIP -1
> IF EOF()
> lFound = .F. && No record for this Pcode
> ELSE
> lFound = .T. && We are on the current price
> ENDIF
>ENDIF
>* Restore entry state
>SET FILTER TO &cSaveFilter
>SELECT (cSaveAlias)
>
>RETURN lFound
>ENDPROC
>
>**********************
>I added the parent 'PRODSERV' and child 'SPECPRICE' tables and
>set the RelationExpression to 'relCurrPrice(prsched,code)'
>
>I then added some test fields from each table to a test form.
>
>The relationship expression gets fired and finds the correct record
>but I then get an 'error 67' 'expression evaluator failed' in 'DataEnvironment.OpenTables' method.
>
>Obviously I am doing something wrong. can someone please point me in the correct direction.
>
>Ta very muchly
>Geoff Scott
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only