Solved

Perform calulation and select items when condition is met

Posted on 2013-06-11
5
275 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
[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
  • 2
  • 2
5 Comments
 
LVL 35

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
update statement in oracle 9 51
making a message body variable from an oracle select statement 4 50
format dd/mm/yyyy parameter 16 58
oracle query 3 34
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 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