Helen -

thanks for patience on this, but I'm guessing that variables within
an "AS" clause is not supported in my programming environment. I
simplified the equation for testing purposes.


For instance this works;

SELECT (69.1*(40.1-LATITUDE)) AS DISTANCE, NAME FROM WEBDATA

and this works

SELECT (69.1*(40.1-LATITUDE)) AS DISTANCE, NAME FROM WEDATA WHERE
EDATE = :var1
params["var1] = date()

BUT this does not work

SELECT :lat1-LATITUDE AS DISTANCE, NAME FROM WEBDATA

The above line throws off an -802 error

I'm wondering if a stored procedure could be used to generate the
rowset.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> 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
>