Query for decimal part
Francisco Roldan wanted to select rows where a decimal(26,8) column has a non-zero fractional part. John Carlson suggested comparing the value with trunc(amount). Paul Watson suggested decimal_col != decimal_col::INT, which Carlson confirmed worked on 9.30. Carlson also noted decimal_col != trunc(decimal_col) should work. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi Everybody,
I got a table with a column named amount
of type decimal(26,8) .
I need to make a query to select the rows
with no-integers values on this column (amount).
I mean the rows with decimal part on its value.
Something like this :
select * from shclog
where get_decimals(amount) <> 0
Assuming that the function get_decimals return the
decimal part of the column amount.
Does exist a function like this ?
Thanks in advance
sending to informix-list
On Thu, 21 Aug 2003 10:32:29 -0600, Francisco Roldan
<froldan@5b.com.gt> wrote:
>
>Hi Everybody,
>I got a table with a column named amount
>of type decimal(26,8) .
>I need to make a query to select the rows
>with no-integers values on this column (amount).
>I mean the rows with decimal part on its value.
>
>Something like this :
>
>select * from shclog
>where get_decimals(amount) <> 0>
>Assuming that the function get_decimals return the
>decimal part of the column amount.
>
>Does exist a function like this ?
>
Well, not exactly, but this may do . . . . the trunc() frunction.
e.g.
select * from shclog
where (amount-trunc(amount)) = 0
amount is 57.95
trunc(amount) would be 57
difference would be 0.25
amount is 34.00
trunc(amount would be 34
difference would be 0
I haven't tried it but
select *
from table
where decimal_col != decimal_col::INT
should work
Francisco Roldan wrote:
>
> Hi Everybody,
> I got a table with a column named amount
> of type decimal(26,8) .
> I need to make a query to select the rows
> with no-integers values on this column (amount).
> I mean the rows with decimal part on its value.
>
> Something like this :
>
> select * from shclog
> where get_decimals(amount) <> 0>
> Assuming that the function get_decimals return the
> decimal part of the column amount.
>
> Does exist a function like this ?
>
> Thanks in advance
> sending to informix-list
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #
On Thu, 21 Aug 2003 19:04:01 +0100, Paul Watson <paul@oninit.com>
wrote:
>I haven't tried it but
>
>select *
>from table
>where decimal_col != decimal_col::INT>
>should work
>
That works too . . .on 9.30 . . .
On Thu, 21 Aug 2003 14:21:05 -0400, John Carlson
<john_carlson@whsmithusa.com> wrote:
>On Thu, 21 Aug 2003 19:04:01 +0100, Paul Watson <paul@oninit.com>
>wrote:
>
>>I haven't tried it but
>>
>>select *
>>from table
>>where decimal_col != decimal_col::INT>>
>>should work
>>
>
>That works too . . .on 9.30 . . .
Of course . . .
select *
from table
where decimal_col != trunc(decimal_col)
should work too.