At 10:39 AM 10/01/2009, you wrote:

>Helen thanks for the great explanation. However, after following your
>example I still get an error. This is the code now (the code is dbase
>and it works fine with dbase tables with exactly the same structure):
>
>q.sql = "SELECT (69.1*(:lat1-LATITUDE))+(69.1*(:lat2-LATITUDE))+(69.1*
>(:lon1-(LONGITUDE)*COSINE))+(69.1*(:lon2-LONGITUDE)*COSINE)) AS
>DISTANCE, NAME FROM WEBDATA"
> q.params["lat1"] = 40.7409
> q.params["lat2"] = 40.7409
> q.params["lon1"] = -73.9997
> q.params["lon2"] = -73.9997
>
>I somehow get the idea that the problem is that there are too many
>brackets in the sql statement. I still get an -804 and sometimes I
>get an -104 if I remove some of the brackets. I can make the
>statement work by taking out all of the brackets, but, of course, the
>value of DISTANCE is then wrong as the formula is not grouped
>properly.

You really *do* need to separate yourself from "what works in dBase". dBase uses an idiosyncratic SQL-like layer that Borland invented over the Paradox QBE engine very early in the global history of the SQL language. It always did have logic bugs that Borland never fixed in the BDE engine. You really will need to review *all* of your dBase statements - both logic and syntax - to upgrade them to Firebird's [more or less] standard SQL. The Road to Hell is paved with undocumented workarounds to SQL errors in the BDE engine.

For complex expression statements in particular, I do strongly recommend breaking up the statements into visible, manageable pieces, using q.SQL.Add('chunk') rather than assigning directly to the Text property of the q.SQL structure...although I don't always practise what I preach! I have the blessing of having IBO's SQL editor available, which is even better for troubleshooting than well-thought-out lines in SQL.Strings.

You have (at least) two potential sources of error with bracketing in SQL statements, viz., (1) the logic of the expression and (2) the logic of the SQL syntax. (2) gives you DSQL errors, whereas (1) could be syntactically correct but logically wrong, due to the parsing being in an order different to what you assumed (or what was assumed correct based on the way the Paradox QBE engine parsed it).

The errors here I suspect are two. One is your bracketing of the LONGITUDE column reference in the third expression. I can't guess what you intended by that. Some bracketing in SQL is optional but I don't know off-hand whether Firebird's DSQL parser is happy to ignore it. In the fourth calculation, you don't make this mistake, but you have one unmatched right bracket, which the parser would certainly object to.

SELECT
(69.1*(:lat1-LATITUDE))
+(69.1*(:lat2-LATITUDE))
// +(69.1*(:lon1-(LONGITUDE)*COSINE))
+(69.1*(:lon1-LONGITUDE)*COSINE)
// +(69.1*(:lon2-LONGITUDE)*COSINE))
+(69.1*(:lon2-LONGITUDE)*COSINE)
AS DISTANCE,
NAME
FROM WEBDATA

./heLen