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.

What's the right way to do this query?

In advance thanks a lot for your help.

SM