Identifying floating point or fractional numbers in sql [message #286199] |
Thu, 06 December 2007 19:15  |
pweill
Messages: 11 Registered: December 2007
|
Junior Member |
|
|
Please help if you can:
I need to write an SQL statement which looks in an Oracle table to find any amount in a field that is only fractional. For example: A table called purchase_orders shows a field called quantity_billed. Some of the quantity billed are fractions (.6014) and some (most) are whole numbers. I only want to select the fractional numbers since these are problems that we will encounter later. Thanks for any help that anyone can give!
|
|
|
|
|
Re: Identifying floating point or fractional numbers in sql [message #286266 is a reply to message #286201] |
Fri, 07 December 2007 01:23   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
You could use MOD as well:
SQL> WITH numtab AS
2 (
3 SELECT 0.001 numcol FROM dual UNION ALL
4 SELECT 9 numcol FROM dual UNION ALL
5 SELECT 3.02 numcol FROM dual UNION ALL
6 SELECT 1.1 numcol FROM dual UNION ALL
7 SELECT 7 numcol FROM dual UNION ALL
8 SELECT 1 numcol FROM dual UNION ALL
9 SELECT 3 numcol FROM dual UNION ALL
10 SELECT 2.000 numcol FROM dual
11 )
12 SELECT numcol
13 FROM numtab
14 WHERE MOD(numcol, 1) != 0
15 /
NUMCOL
----------
.001
3.02
1.1 MOD returns the remainder (i.e. fractional part) of a integer division. Read more about it in the Oracle documentation.
MHE
[edit]The "WITH" part is just there to create a sample table on the fly. Just look at the select.
[Updated on: Fri, 07 December 2007 01:24] Report message to a moderator
|
|
|
|
|
|
|