Hi,

In Informix, I have a char(6) column in which decimals are stored. I
need to sort in the numeric order. I have seen couple of query posted
before which deals with numeric stored in a char field but I'm looking
for decimals stored in the char.

I need the o/p in following format -

10.1
10.2
10.3
10.10
10.22
10.29
10.4

Thanks & Regards
Abhinav

Jaini said:
>
> Hi,
>
> In Informix, I have a char(6) column in which decimals are stored. I
> need to sort in the numeric order. I have seen couple of query posted
> before which deals with numeric stored in a char field but I'm looking
> for decimals stored in the char.
>
> I need the o/p in following format -
>
> 10.1
> 10.2
> 10.3
> 10.10

Actually, 10.10 = 10.1

> 10.22
> 10.29
> 10.4

If you have IDS 9, you can cast them to DECIMAL. If not, you can try
adding 0.00 to each.

SELECT col:ECIMAL FROM tab ORDER BY 1

or

SELECT col + 0.00 FROM tab ORDER BY 1

Neither option has been tested. )

--
Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

did i mention i like nulls? heck, i even go so far as to say that all
columns in a table except the primary key could/should be nullable. this
has certain advantages, for example, if you need to insert a child record
and you don't have a parent row for it, just do an insert into the parent
table with the primary key value (everything else null), and voila,
relational integrity is preserved. but this is, admittedly, a bit
controversial among modellers.

--r937, dbforums.com

Jaini wrote:
> Hi,
>
> In Informix, I have a char(6) column in which decimals are stored. I
> need to sort in the numeric order. I have seen couple of query posted
> before which deals with numeric stored in a char field but I'm looking
> for decimals stored in the char.
>
> I need the o/p in following format -
>
> 10.1
> 10.2
> 10.3
> 10.10
> 10.22
> 10.29
> 10.4
>

You don't say which version you have.

For 7.x, you SELECT char_column + 0 ... ORDER BY 1

For 9.x, you do as for 7.x or SELECT char_column:ECIMAL ... ORDER BY 1

You'd be better off fixing the table though.

--
rh

if you want these sorted in that order then these are not decimals so
you are unlikely to

10.22 is a smaller number than 10.3 and bugg er

ten point twenty two is not a decimal number and may well be bigger
than ten point three

Obnoxio,

It didn't help me as, the in the result the deciamls are padded with
ZERO's and there is no difference between 10.4 & 10.40. I want the
result in following format -

10.1
10.4
10.11
10.40
10.45

But casting or adding 0.00 is resulting

(expression)
10.10000000
10.11000000
10.40000000
10.40000000
10.45000000

Kindly suggest other way.

Thanks for your time.

Jaini said:
>
> Obnoxio,
>
> It didn't help me as, the in the result the deciamls are padded with
> ZERO's and there is no difference between 10.4 & 10.40. I want the
> result in following format -
>
> 10.1
> 10.4
> 10.11
> 10.40
> 10.45
>
> But casting or adding 0.00 is resulting
>
> (expression)
> 10.10000000
> 10.11000000
> 10.40000000
> 10.40000000
> 10.45000000

Well, yes, in a decimal number, 10.4 = 10.40. So what you are saying is
that while it contains a decimal point, the number is not, in fact, a
decimal at all.

So what are you actually trying to do?

--
Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

did i mention i like nulls? heck, i even go so far as to say that all
columns in a table except the primary key could/should be nullable. this
has certain advantages, for example, if you need to insert a child record
and you don't have a parent row for it, just do an insert into the parent
table with the primary key value (everything else null), and voila,
relational integrity is preserved. but this is, admittedly, a bit
controversial among modellers.

--r937, dbforums.com

Jaini wrote:
> Hi,
>
> In Informix, I have a char(6) column in which decimals are stored. I
> need to sort in the numeric order. I have seen couple of query posted
> before which deals with numeric stored in a char field but I'm looking
> for decimals stored in the char.
>
> I need the o/p in following format -
>
> 10.1
> 10.2
> 10.3
> 10.10
> 10.22
> 10.29
> 10.4
>
> Thanks & Regards
> Abhinav

you do realize that the order that you want is neither in char order nor
numeric order, right? in neither 10.10 would be bigger than 10.3

in 9.4 you can order by expressions not in the select list, eg

select c from t order by c+0, length(c)

yields

10.1
10.10
10.11
10.2
10.22
10.29
10.3
10.4

just write an SP that sorts stuff by whatever warped order you like

--
Ciao,
Marco
__________________________________________________ ____________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

10.four is smaller than 10.forty in this alternative decimal notation

Obnoxio,

I agree with you. This is what I'm looking for -

I need to sort the numbers considering only after decimals so that 3
comes before 22 in 10.3 & 10.22.

Thanks

Jaini said:
>
> Obnoxio,
>
> I agree with you. This is what I'm looking for -
>
> I need to sort the numbers considering only after decimals so that 3
> comes before 22 in 10.3 & 10.22.

Pffft.

Something like

SELECT col, TRUNC(col+0), (col+0 - TRUNC(col+0))
FROM tab
ORDER BY 2, 3

?

--
Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

did i mention i like nulls? heck, i even go so far as to say that all
columns in a table except the primary key could/should be nullable. this
has certain advantages, for example, if you need to insert a child record
and you don't have a parent row for it, just do an insert into the parent
table with the primary key value (everything else null), and voila,
relational integrity is preserved. but this is, admittedly, a bit
controversial among modellers.

--r937, dbforums.com