How to rewrite this procedure by using joins?

Question

CREATE OR REPLACE PROCEDURE sample
IS
l_sum_1   NUMBER;
l_sum_2   NUMBER;
l_res  NUMBER;
BEGIN
SELECT   SUM (c1)
      + SUM (c2)
      + SUM (c3)
      + SUM (c4)
      + SUM (c5)
      + SUM (c6)
      + SUM (c7)
      + SUM (c8)
      + SUM (c9)
      + SUM (c10)
 INTO l_sum_1
 FROM tt1;

SELECT SUM (p1) INTO l_sum_2 FROM tt2;
l_res := l_sum_1 - l_sum_2;
END;

Please explain what the code should be doing. Sample data and desired results are very helpful for such an explanation. And . . . this code is only referencing one table once. `JOIN` doesn't seem necessary. — Gordon Linoff, Mar 11 '21 at 14:15
Instead of using two select statement,need to sum it by using joins — vanathy srilakshmi, Mar 11 '21 at 14:21
If the two sums you are calculating currently are correct, then your question makes no sense. There is no relationship between the two tables (at least as far as the sums go); why would you join the tables? Why would you use anything *other than* the two `select` statements you have now? What is the **real** issue here? — , Mar 11 '21 at 14:27
There is a relationship between both tables(cust_code column),in that case how to join these tables and get the sum of it — vanathy srilakshmi, Mar 11 '21 at 14:36
Even though you say there is cust_code_column, your SQL does not depend on this relationship, so there is nothing to JOIN on. Please post sample data tables and example result. — stifin, Mar 11 '21 at 14:39

score 0 · Answer 1 · answered Mar 11 '21 at 14:50

Instead of having two separate queries, the calculation can be combined into one query to reduce the number of context switches.

As others have said in the comments, there doesn't seem to be any need or way to JOIN the tables together based on the logic you provided in your sample procedure.

CREATE OR REPLACE PROCEDURE sample
IS
    l_res   NUMBER;
BEGIN
    SELECT   (SELECT   SUM (c1)
                     + SUM (c2)
                     + SUM (c3)
                     + SUM (c4)
                     + SUM (c5)
                     + SUM (c6)
                     + SUM (c7)
                     + SUM (c8)
                     + SUM (c9)
                     + SUM (c10)
                FROM tt1)
           - (SELECT SUM (p1) FROM tt2)
      INTO l_res
      FROM DUAL;
END;

score 0 · Answer 2 · answered Mar 11 '21 at 16:55

Is this what you ware looking for?

SELECT l_sum_1, l_sum_2, l_sum_1 - l_sum_2;
INTO l_sum_1, l_sum_2, l_res
FROM (SELECT ( SUM(c1) + SUM (c2) + SUM(c3) + SUM(c4) + SUM(c5) + SUM(c6) +
               SUM(c7) + SUM(c8) + SUM(c9) + SUM(c10)
             ) as l_sum_1
     FROM tt1
    ) tt1 CROSS JOIN
    (SELECT SUM(p1) as l_sum_2
     FROM tt2
    );

You probably don't need to set the first two variables but I included them anyway.

How to rewrite this procedure by using joins?

2 Answers2