--- In firebird-support@yahoogroups.com, "davidmarcelo2003"
<davidmarcelo2003@y...> wrote:

> My english is not really good, i hope you understand...

My is'nt better :)

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

Note now Distinct and Group By produces ordered result, but I don't
recommend to rely on this in the future, internal algorithm can be
changed. Add needed Order By, optimizer will avoid unnecesary sorting
if it is already done when performing query.

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

It is Delphi message. You created persistent TField on Fieldvalue
but column now is reported as CAST. Use alias to bring name back

CAST (TABLE1.fieldvalue as integer) as fieldvalue

or drop and recreate corresponded TField. If you will need mention
such a column in Order By, use it's position, in this case 4.

Best regards,
Alexander.