Perform calulation and select items when condition is met

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'
AlphaMig1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
select ...
from your_table
where round(t.fee_net/t.svngs_net,2) = .2
and svngs_net != 0
0
 
jonnidipCommented:
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
 
AlphaMig1Author Commented:
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
 
jonnidipCommented:
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
 
AlphaMig1Author Commented:
Yes, they are numeric.  And your suggestion worked!
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.