simsim simsim - 2 months ago 7
SQL Question

Conditional where statement and comparison operator

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?

Answer

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