At 03:52 PM 25/02/2004 +0000, you wrote:

>My english is not really good, i hope you understand...
>I don't know whether this is the right newsgroup for this question.
>
>I have a query to a FireBird database from a Visual Delphi 7 program.
>The SQL is like this:
>
>SELECT DISTINCT doc.field1, doc.field2, , TABLE1.id,
>TABLE1.fieldvalue,
>TABLE1.fieldname as fieldname0, TABLE2.fieldname as fieldname1 FROM
>DOCUMENTO doc JOIN TABLE1 ON (TABLE1.documento_id=d.id) AND
>(TABLE1.fieldvalue=TABLE2.fieldvalue) AND
>(TABLE.linenumber=TABLE2.linenumber) WHERE
>((doc.creation_date='02/28/2002')) and (UPPER(TABLE1.fieldname) =
>UPPER(cast('2005' as VARCHAR(150)))) and (TABLE2.fieldname <= cast
>('1000'
>as DOUBLE PRECISION))
>
>The fact is that "fieldvalue" field is a field defined as string,
>storing a number of page, so the result of the query is order like
>strings:
>
>10
>100
>2
>20
>27
>8
>
>I need to convert the result to numeric to obtain the number ordered.
>(I can't change the database field design)
>
>I have try this (with Cast function):
>
>SELECT DISTINCT doc.field1, doc.field2, TABLE1.id, CAST
>(TABLE1.fieldvalue
>as integer), TABLE1.fieldname as fieldname0, TABLE2.fieldname as
>fieldname1
>FROM DOCUMENTO doc JOIN TABLE1 ON (TABLE1.documento_id=d.id) AND
>(TABLE1.fieldvalue=TABLE2.fieldvalue) AND
>(TABLE.linenumber=TABLE2.linenumber) WHERE
>((doc.creation_date='02/28/2002')) and (UPPER(TABLE1.fieldname) =
>UPPER(cast('2005' as VARCHAR(150)))) and (TABLE2.fieldname <= cast
>('1000'
>as DOUBLE PRECISION))
>but is not working.
>
>I have tried this and I receive and error saying "Field Fieldvalue not
>found", the sql works fine without the cast function.

Really? You have mixed-syntax joins in there. If this is Firebird 1.5 I
would expect this query to fail under any circumstances, first because of
the mixed syntax and second because you are mixing table aliases with table
identifiers.

Also, you have several typos in your query ("TABLE instead of TABLE1, "d"
instead of "doc", etc.)

>What's the right way to do this query?

SELECT DISTINCT
doc.field1,
doc.field2,
t1.id,
CAST (t1.fieldvalue as integer) as fvalue,
t1.fieldname as fieldname0,
t2..fieldname as fieldname1
FROM DOCUMENTO doc
JOIN TABLE1 t1
ON t1.documento_id=doc.id
/* explicit join */
JOIN TABLE2 t2
ON (T1.fieldvalue=T2.fieldvalue)
AND (T1.linenumber=T2.linenumber)
WHERE
doc.creation_date='02/28/2002'
and UPPER(t1.fieldname) = UPPER(cast('2005' as VARCHAR(150)) /*WEIRD!!*/
and t2.fieldname <= cast ('1000' as DOUBLE PRECISION) /*also WEIRD!! */

/heLen