Home » SQL & PL/SQL » SQL & PL/SQL » Identifying floating point or fractional numbers in sql
Identifying floating point or fractional numbers in sql [message #286199] Thu, 06 December 2007 19:15 Go to next message
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 #286200 is a reply to message #286199] Thu, 06 December 2007 19:17 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
WHERE quantity_billed-round(quantity_billed) <> 0
Re: Identifying floating point or fractional numbers in sql [message #286201 is a reply to message #286200] Thu, 06 December 2007 19:23 Go to previous messageGo to next message
pweill
Messages: 11
Registered: December 2007
Junior Member
Thank you!!!!
Re: Identifying floating point or fractional numbers in sql [message #286266 is a reply to message #286201] Fri, 07 December 2007 01:23 Go to previous messageGo to next message
Maaher
Messages: 7062
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

Re: Identifying floating point or fractional numbers in sql [message #286270 is a reply to message #286266] Fri, 07 December 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Smart one! ./fa/2115/0/

Regards
Michel
Re: Identifying floating point or fractional numbers in sql [message #286272 is a reply to message #286266] Fri, 07 December 2007 01:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe it is just me, but I find this one more intuitive:

where col <> trunc(col)

Re: Identifying floating point or fractional numbers in sql [message #286288 is a reply to message #286272] Fri, 07 December 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was also my first thought but it is nice to see someone thinking differently.

Regards
Michel
Re: Identifying floating point or fractional numbers in sql [message #286448 is a reply to message #286199] Fri, 07 December 2007 07:26 Go to previous message
pweill
Messages: 11
Registered: December 2007
Junior Member
I really appreciate everyone's help! Thanks!!!!
Previous Topic: How to convert row col to row ?
Next Topic: Create table as select generates ORA-03113
Goto Forum:
  


Current Time: Wed Dec 07 04:41:39 CST 2016

Total time taken to generate the page: 0.45403 seconds