title: PL/SQL - Optional conditions in where-clause - without dynamic sql?
body: <p>I have a query where not all conditions are necessary. Here's an example of what it looks like when all conditions are used:</p>
  
  <pre><code>select num
  from (select distinct q.num
         from cqqv q
         where q.bcode = '1234567' --this is variable
               and q.lb = 'AXCT' --this is variable
               and q.type = 'privt' --this is variable
               and q.edate &gt; sysdate - 30 --this is variable
         order by dbms_random.value()) subq
  where rownum &lt;= 10; --this is variable
  </code></pre>
  
  <p>The parts marked as <code>--this is variable</code> are the parts that, well, vary! If a condition is NOT specified, then there is no default value. For example, if the input specifies "*" for q.type (but leaves everything else the same), then the query should match everything for type, and execute as:</p>
  
  <pre><code>select num
  from (select distinct q.num
         from cqqv q
         where q.bcode = '1234567' --this is variable
               and q.lb = 'AXCT' --this is variable
               --and q.type = 'privt' --this condition ignored because of "type=*" in input
               and q.edate &gt; sysdate - 30 --this is variable
         order by dbms_random.value()) subq
  where rownum &lt;= 10; --this is variable
  </code></pre>
  
  <p>I know it is possible to use dynamic sql to build this query on the fly, but I am wondering what sort of performance problems this could cause, and if there is a better way to do this.</p>