1

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.

  • Put the values into variables and then divide them? – LowlyDBA Jan 25 '16 at 19:22
  • Try (SELECT SUM(Month_Production) FROM Production) / (SELECT COUNT(Month_Production) FROM Production) instead (check the parentheses). Other than that the query can be written using only one SELECT, the subqueries are redundant. – jkavalik Jan 25 '16 at 20:10
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.