title: Unable to create a Oracle function for the given scenario
body: <p>Trying to solve the following question:</p>
  <p>Create a Function named 'find_balance' which takes invoice_id as input and returns the balance_status of type varchar.</p>
  <p>Design rules:</p>
  <ol>
  <li>If the balance of the given invoice id is equal to zero,then display the status as 'The payment has been Completed'</li>
  <li>If the balance of the given invoice id is greater than zero,then display the status as amount 'yet to be paid' for example, If their <code>balance = 500</code> then display 500 yet to be paid</li>
  </ol>
  <p>I had the following solutions, but none of it work:</p>
  <p>Solution 1:</p>
  <pre><code>CREATE OR REPLACE FUNCTION find_balance ( invoice_id  IN  invoice_line.invoice_id%type) 
  RETURN VARCHAR2
  IS      balance_status invoice.status%type; 
  
          BEGIN
  
          SELECT 
          CASE WHEN balance = 0 THEN 'The payment has been Completed'
          CASE WHEN balance &gt; 0 THEN TO_CHAR(balance) || ' yet to be paid'
          ELSE 'Invalid Payment'
          END tmp_status INTO balance_status
          FROM invoice inv 
          , invoice_line inl
          WHERE inv.id = inl.invoice_id
          AND inl.invoice_id = invoice_id;
          
          RETURN(balance_status);
          END find_balance; 
  </code></pre>
  <p>Solution 2:</p>
  <pre><code>CREATE OR REPLACE FUNCTION find_balance ( invoice_id  IN  invoice_line.invoice_id%type) 
  RETURN VARCHAR2
  IS      balance_status invoice.status%type; 
          v_bal invoice.balance%type; // Assumed this is the way to create a local variable
          BEGIN
          SELECT balance INTO v_bal
          FROM invoice inv 
          , invoice_line inl
          WHERE inv.id = inl.invoice_id
          AND inl.invoice_id = invoice_id;
          
          IF      v_bal = 0 THEN balance_status := 'The payment has been Completed';
          ELSIF   v_bal &gt; 0 THEN balance_status := TO_CHAR(v_bal) ||' yet to be paid';
          ELSE    balance_status := 'Invalid payment';
          END IF
          
          RETURN(balance_status);
          END find_balance; 
  </code></pre>
  <p>Guess something is wrong syntactically, please correct me where am I going wrong?</p>