CODEWITHSUNDEEP
oracle-databaseoracle11g
Devashri B.
Devashri B.

Reputation: 2713

how to compare one value against 2 values in Oracle

I want to compare a a value against 2 values without using OR or DECODE. The value I want to compare with two values is the one which I am getting as a return code of a function. If I use OR or DECODE then I have to call function twice and it gives performance hit. Currently I am coding as below

select *
from table1 t1, table2 t1
where t1.empid = t2.empid
and myfunction(t2.balance) = t1.total OR myfunction(t2.balance) = -1

Please suggest if there is a way to call function once and compare with 2 values.

Upvotes: 0

Views: 63

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

To shorten your code you could use IN operator which acts like OR.

select *
from table1 t1
join table2 t1 on
  t1.empid = t2.empid
and myfunction(t2.balance) in (t1.total, -1)

I've also replaced old-fashioned join syntax in where clause for JOIN keyword and you're advised to be using that in your future SQL journeys.

Good thing to know would be that even though you call the function twice, most modern databases would actually call it only once, so I wouldn't be that much concerned about it.

Upvotes: 1

Related Questions