/ Character in a stored procedure

oracle-sql-developerplsql

I am trying to recompile/update a stored procedure in SQL Developer that uses a few division operations to populate tables.

A sample section looks like this:

  Insert Into Production(A, B, C)
  SELECT A, B, (SELECT SUM(Month_Production) 
  FROM Production / SELECT COUNT(Month_Production) FROM Production2)  FROM DUAL;

The problem I run into is in the subquery, it thinks the division symbol is an "END OF FILE" marker, and then can't process the rest of the procedure (There are about 10 of these types of insert statements in this procedure). I have been researching online how to "escape" this character.

I tried using a \ to escape it out, 

   Insert Into Production(A, B, C)      
   SELECT A, B, (SELECT SUM(Month_Production)
   FROM Production \/ SELECT COUNT(Month_Production) FROM Production2)
   FROM DUAL;

but this is not working to help compile. Any idea on how I can compile this while keeping the division operations in here?

Thanks!

edit:I rewrote the queries as I think the focus was on my structure and not the problem. The queries I posted were just quick write ups to demonstrate what I am having issues with. The queries in question are very long and work fine, I pulled a "Backup" query from the procedure but even when I rerun it it feels the / is the end of the file and not a division symbol. I've tried to rewrite it with various ways to surround the code with () but still am having issues.

Best Answer

Your outer select is incomplete, it has no FROM clause.

Once that's fixed your query may work, if not try putting each of the sub selects inside of a pair of parenthesis, and possibly the whole operation.

Insert Into Production(A, B, C)      
SELECT A
     , B
     , ( (SELECT SUM(Month_Production) FROM Production)
       / (SELECT COUNT(Month_Production) FROM Production)
       ) C
  from some_table;  -- <== This is missing

By the way since both the numerator and the denominator are coming from the same table, this would be a more efficient query:

SELECT A
     , B
     , (SELECT SUM(Month_Production) 
             / COUNT(Month_Production)
          FROM Production
       ) C
  from some_table;

Since it only has to access the whole Production table once instead of twice

Related Question