Thanx Alexander!!!

SM

--- In firebird-support@yahoogroups.com, "Alexander V.Nevsky"
<ded@h...> wrote:

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