asked on

oracle query

select dn.donor_id,
       salutation,
       first_name,
       middle_name,
       last_name,
       description,
       coll_date,
       life_donations,
       life_donations/8 as mile_stone
from donations@pdon_new d,
       donors@pdon_new dn,
       master_codes mc
where d.donor_id = dn.donor_id
and unit_id is not null
and mc.udf1 = blood_type
and mc.code_type = 'ABO'
and coll_date = ' 31-may-2013'

My question is - I divide life_donations by 8 to see who has reached a gallon mile. In my report I wasnt to show the remainder as 0 so when I divide life_donations by 8 I need to show 0 and only those have to be on the report.
life-donations.xls

Sharath S

Do you want to truncate the fration part?

select dn.donor_id,
         salutation,
         first_name,
         middle_name,
         last_name,
         description,
         coll_date,
         life_donations,
         trunc(life_donations/8) as mile_stone
  from donations@pdon_new d,
           donors@pdon_new dn,
         master_codes mc
 where d.donor_id = dn.donor_id
   and unit_id is not null
   and mc.udf1 = blood_type 
   and mc.code_type = 'ABO'
   and coll_date = ' 31-may-2013'

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

Is remainder 10G?

Sharath S

Yes

Sharath S

typo.

select dn.donor_id,
         salutation,
         first_name,
         middle_name,
         last_name,
         description,
         coll_date,
         life_donations,
         trunc(life_donations/8) as mile_stone
  from donations@pdon_new d,
           donors@pdon_new dn,
         master_codes mc
 where d.donor_id = dn.donor_id
   and unit_id is not null
   and mc.udf1 = blood_type 
   and mc.code_type = 'ABO'
   and coll_date = ' 31-may-2013'
   and remainder(life_donations,8) = 0

Open in new window

David VanZandt

i can't add to the author's query, but the typo in line 17 above is bothersome. Unless all column entries have a leading space and a lower-case month, you are going to need a conversion function. Assuming that coll_date (LOVE the abbreviation) is datatype DATE, then the comparison should be rewritten as ...coll_date = TO_DATE('31-MAY-2013','DD-MON-YY').

anumoses

ASKER

thanks

PortletPaul

>>Is remainder 10G?
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions133.htm

and a very close cousin is MOD() (short for modulus)
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions088.htm