Solved

Perform calulation and select items when condition is met

Posted on 2013-06-11
5
273 Views
Last Modified: 2013-06-11
I am using PL/SQL to write a query to extract the following:

I need to calculate:
round(t.fee_net/t.svngs_net,2)
then I need to select only those items where the result is = '.20'
I don't want to select any items where svngs_net is '0'
0
Comment
Question by:AlphaMig1
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 39237722
select ...
from your_table
where round(t.fee_net/t.svngs_net,2) = .2
and svngs_net != 0
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 39237731
While I think there could be a performance issue (it should be checked by looking at the actual indexes of your tabled), I would do something like this:
select * from myTable where round(t.fee_net/t.svngs_net,2) = 0.20

Open in new window

Isn't it?

Regards.
0
 

Author Comment

by:AlphaMig1
ID: 39237797
Thanks for your response jonnidip,

Using the formula you suggested, I get an error that divisor is '0' so I tried:

select
(case
      when b.svngs_net <> '0'
        then round(t.fee_net/t.svngs_net,2) else 0 end)as fee_percent

from myTable t

where
round(t.fee_net/t.svngs_net,2)='.20'

But is this the most efficient way to do this?
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 39237835
OK, I understand what was the problem with '0'.
You may go with your way of using a subquery, but you need to modify it as:
select 
		t.*,
		(case when b.svngs_net <> 0
				then round(t.fee_net/t.svngs_net,2) else 0 end)as fee_percent
from myTable t
where 
t.fee_percent=0.20

Open in new window


You have surrounded '0' and '.20' with single quotes: are these fields varchar or numeric?

Regards.
0
 

Author Comment

by:AlphaMig1
ID: 39237886
Yes, they are numeric.  And your suggestion worked!
Thank you
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

832 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