title: Apply dynamic where condition in the database view (oracle - 12c, mysql 5+)
body: <p>Can I create a view with dynamic "WHERE" clause. This where clause will be built based on user and certain conditions</p>
  
  <p>This is my <strong><em>transaction table</em></strong>:</p>
  
  <pre><code>+--------+----------+------------+
  | Job ID | Customer | Department |
  +--------+----------+------------+
  | 1      | ABC      | Tanker     |
  | 2      | ABC      | Dry        |
  | 3      | MEX      | Tanker     |
  | 4      | MEX      | Dry        |
  +--------+----------+------------+
  </code></pre>
  
  <ul>
  <li>User A - require to see all transaction  </li>
  <li>User B - require to see all Transactions of Tanker department  </li>
  <li>User C - require to see all transactions of Tanker department but belongs to customer ABC</li>
  </ul>
  
  <p>My user wise setup has header details</p>
  
  <p><strong><em>Access key</em></strong> - this is binary code for each segment I used to filter data (first position is department, second is customer and 0 mean all and 1 mean select/limited access)</p>
  
  <p><strong>Header:</strong></p>
  
  <pre><code>+------+------------+
  | User | Access key |
  +------+------------+
  | A    | 00         |
  | B    | 10         |
  | C    | 11         |
  +------+------------+
  </code></pre>
  
  <p><strong>Detail table:</strong></p>
  
  <pre><code>+------+------------+--------+
  | User |   Segment  | Value  |
  +------+------------+--------+
  | B    | Department | Tanker |
  | C    | Department | Tanker |
  | C    | Customer   | ABC    |
  +------+------------+--------+
  </code></pre>
  
  <p>Now I need to filter the data based on above user wise setup. Instead of fixing the where clause in the view to fetch the data, can I build the where clause dynamically based on the user and then pass and execute with the select query?
  Please help, I have 2 dbs (MySQL and Oracle)</p>
  
  <p>Even alternative suggestions to archive this requirement also most welcome.</p>