Solved

oracle query

Posted on 2013-06-07
8
311 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
8 Comments
 
LVL 40

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 40

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
 
LVL 40

Expert Comment

by:Sharath
ID: 39229846
Yes
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

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 48

Expert Comment

by:PortletPaul
ID: 39231180
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query in Oracle forms Builder 2 41
Oracle and DateTime math 6 26
passing parameters to sql script oracle 4 26
sort a spool into file output in oracle 1 22
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now