title: Oracle View Conditional Logic
body: <p>I have a currency conversion app where when you give it a from currency and a to currency, it returns the exchange rate, e.g.
  From USD, To EUR, Rate=2.10</p>
  
  <p>The currencies are held in MY_TABLE.</p>
  
  <p>I want to extend this app to handle cases where a conversion doesn't exist. To do this, I'll reverse the calculation, e.g.</p>
  
  <p>if From USD, To EUR does_not_exist then
  return From USD, To EUR, Rate=1 / 2.10.</p>
  
  <p>I will create a view that does this conversion automatically. I am new to view and am asking for a steer on now to do this. Here is my pseudo code. I know it's not right but am I on the right track?</p>
  
  <pre><code>CREATE OR REPLACE VIEW MY_VIEW (FROM_CURR, TO_CURR, RATE) AS
  DECLARE
    rates_found number;
  BEGIN
        select count(*)
        into   rates_found
        from   MY_TABLE
        where  rownum = 1
        and FROM_CURR = ? and TO_CURR = ?
  
        if rates_found = 1 then
          select FROM_CURR, 
                 TO_CURR, 
                 RATE
          from   MY_TABLE
          and FROM_CURR = ? and TO_CURR = ?
  
        else
          select FROM_CURR, 
                 TO_CURR, 
                 1 / RATE AS RATE
          from   MY_TABLE
          and FROM_CURR = ? and TO_CURR = ?
  END;
  </code></pre>