Solved

# Perform calulation and select items when condition is met

Posted on 2013-06-11
270 Views
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
Question by:AlphaMig1
• 2
• 2

LVL 34

Accepted Solution

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

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
``````
Isn't it?

Regards.
0

Author Comment

ID: 39237797

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

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
``````

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

Regards.
0

Author Comment

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

## Featured Post

### Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function