0

I'm trying to reduce the size of my sql where statement to eliminate redundant clauses "remove the union statements.

My statement is like this:

SELECT
   col1, col2, col3...etc
FROM
   someTabe
WHERE
   col1 = :param
   and :sType = 1
UNION
SELECT
   col1, col2, col3...etc
FROM
   someTabe
WHERE
   col1 like '%'||:param||'%'
   and :sType = 2

The problem with this statement is that for each :sType possibility, I have to write a select statement and union all the possibilities, and in my case, my sql statements are very long and complicated even without a union

So I have tried to re-write the where statement to something like this:

SELECT
   col1, col2, col3...etc
FROM
   someTabe
WHERE
   CASE WHEN :sType = 1 then col1= :param ELSE col1 like '%'||:param||'%' END

But this fails to run, it needs an operator after the case statement as in this question

So, is there a syntax that can achieve the idea in the above statement?

Community
  • 1
  • 1
sameh.q
  • 1,691
  • 2
  • 23
  • 48
  • can't you just use OR: for ex: WHERE (col1 = :param and :sType = 1) OR (col1 like '%param%' and sType = 2)...?? – Zeina Sep 28 '16 at 05:50
  • 1
    @Zeina, I honestly don't know why I didn't think of the OR statement, I usually tend to avoid OR and I was overthinking of the solution, I think I deserve a down vote xD – sameh.q Sep 28 '16 at 05:52

1 Answers1

2

A simple or might be enough.

SELECT
   col1, col2, col3...etc
FROM
   someTabe
WHERE
   (:sType = 1 and col1 = :param )
or (:sType = 2 and col1 like '%'||:param||'%')

Example of how to use case: Conditional WHERE clause with CASE statement in Oracle

Community
  • 1
  • 1
Rene
  • 10,391
  • 5
  • 33
  • 46