Hi

I have a CHAR-field A_NUMBER. Is it possible to convert this field to an INTEGER field to use it by ORDER BY in a SQL-statement.

select name from address where town like 'qwertz' order by A_NUMBER

Marc

Marc,
Yes. Try something like:

Select name,integer(a_number) as int_num from address where town like 'qwertz' order by int_num;

Andy

Or


C:\>db2 describe table t

Column Type Type
name schema name Length Scale Nulls

------------------------------ --------- ------------------ -------- ----- -----

I SYSIBM CHARACTER 2 0 Yes


1 record(s) selected.


C:\>db2 select * from t

I
--
1
2

2 record(s) selected.


C:\>db2 "select * from t order by integer(i)"

I
--
1
2

2 record(s) selected.


Cheers
Sathyaram


__________________

Thanks,

Is it possible to order the following by a sql-function:

I have the CHAR's :
1, 3b, 10

I want the order
1 -> 3b -> 10

Marc

I think it should be possible ...

Other than int, you can also use DB2 Inbuilt functions in the order by clause ...

I suggest that you try what you want on a test machine ...

Cheers
Sathyaram

You can also ORDER BY simple-integer

simple-integer must be greater than 0 and not greater than the number of columns in the result table. The integer n identifies the nth column of the result table.

For example:

select name, integer(A_NUMBER)
from address
where town like 'qwertz'
order by 2

In any order by clause, the order by column must be in the select list.