asked on

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'

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jonnidip

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.

AlphaMig1

ASKER

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?

jonnidip

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.

AlphaMig1

ASKER

Yes, they are numeric. And your suggestion worked!
Thank you