Solved

oracle query

Posted on 2013-06-07
8
316 Views
Last Modified: 2013-06-08
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
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39229769
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

0
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 39229774
Not sure if I understand the question correctly, but if you want to see only those life donations that can be devided by 8 with reminder 0, you can add additional filter.
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 reminder(life_donations,8) = 0

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 39229816
Is remainder 10G?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 41

Expert Comment

by:Sharath
ID: 39229846
Yes
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39229850
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

0
 
LVL 23

Expert Comment

by:David
ID: 39229936
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').
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39229976
thanks
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39231180
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question