Hi,

I want to query some data using Select like so:

select tfgld102.leac, tfgld102.dbcr
from tfgld102
where tfgld102.cono between :cono.f and :cono.t
and tfgld102.btno between :btno.f and :btno.t
and tfgld102.ttyp between :ttyp.f and :ttyp.t
and tfgld102.year = :year.f
group by tfgld102.leac, tfgld102.dbcr
order by tfgld102.dbcr, tfgld102.leac
selectdo
message(enum.descr$("tfgld.dbcr", tfgld102.dbcr ) & " " & tfgld102.leac)
endselect

The problem is that while I'm sorting "tfgld102.leac", its domain is basically String, which I want to sort it as Long because it is a string containing numbers.

The result is now something like this:
1
10
11
115
118
1196
2
20
21
211
2123

I want it to be like:
1
2
10
11
20
21
. . .

I tried putting
val(tfgld102.leac) in the Order By but it gives me
Unexpected token '(' (error 302)
on compile.

How can I achieve this?

Regards,
Thana

Try to add "val(tfgld102.leac) as vleac" to the select list and order by vleac.

I tried changing it like what you suggested.

select tfgld102.dbcr, val(tfgld102.leac) as vleac
from tfgld102
where tfgld102.cono between :cono.f and :cono.t
and tfgld102.btno between :btno.f and :btno.t
and tfgld102.ttyp between :ttyp.f and :ttyp.t
and tfgld102.year = :year.f
group by tfgld102.leac, tfgld102.dbcr
order by tfgld102.dbcr, vleac

doesn't work. It gives me this error on compile.
"Error SQL: SQLState 42I03: Unknown column 'val' (error 302)"

I also tried changing the same field in Group By but it seems like it doesn't recognize the vleac.

Haha, right, I forgot Infor SQL does not support it. Try this:

long vleac
select tfgld102.leac, tfgld102.dbcr, CAST(tfgld102.leac AS INTEGER) :vleac
from tfgld102
where tfgld102.cono between :cono.f and :cono.t
and tfgld102.btno between :btno.f and :btno.t
and tfgld102.ttyp between :ttyp.f and :ttyp.t
and tfgld102.year = :year.f
group by tfgld102.leac, tfgld102.dbcr
order by tfgld102.dbcr, 3
selectdo
message(enum.descr$("tfgld.dbcr", tfgld102.dbcr ) & " " & tfgld102.leac)
endselect

Thank you giggty. Your solution works like a charm!