Return the sum of the count of two seperate tables PL/SQL

Question

I'm trying to search through two separate tables for the COUNT of a particular value and return it from a function.

  FUNCTION check_parts
    (p_partno         IN  VARCHAR2)
    RETURN NUMBER
  IS
    out_exists  NUMBER;
    sub_exists  NUMBER;
  BEGIN

    SELECT COUNT(*) INTO out_exists
    FROM outline_pn op
    WHERE op.outline_pn = p_partno
    UNION ALL
    SELECT COUNT(*)
    FROM sub_pn sp
    WHERE sp.sub_assy_pn = p_partno;
--      SELECT (SELECT COUNT(*) AS out_exists
--              FROM outline_pn op
--              WHERE op.outline_pn = p_partno) AS out_exists,
--              (SELECT COUNT(*) AS sub_exists
--              FROM sub_pn sp
--              WHERE sp.sub_assy_pn = p_partno) AS sub_exists


      RETURN (out_exists + sub_exists);
  END check_parts;

At first I naively thought that two individual COUNT(*) queries would work...it didn't.

How do I sum the values of two separate COUNT queries and return the result?

Any help is appreciated.

Answer 1

If you still want to have it in one query, here is the option

  create FUNCTION check_parts
    (p_partno         IN  VARCHAR2)
    RETURN NUMBER
  IS
    sum_exists  NUMBER;
  BEGIN

  select count(1) into sum_exists from (
    SELECT outline_pn
    FROM outline_pn op
    WHERE op.outline_pn = p_partno
    UNION ALL
    SELECT sub_assy_pn
    FROM sub_pn sp
    WHERE sp.sub_assy_pn = p_partno);


      RETURN (sum_exists);
  END check_parts;

Answer 2

I do not find a reason to not to using two separate queries. And then you can return the sum of both. You can simply write something like this:

FUNCTION check_parts   
(p_partno         IN  VARCHAR2)
RETURN NUMBER
IS
out_exists  NUMBER;
sub_exists  NUMBER;
BEGIN

SELECT COUNT(*) INTO out_exists
FROM outline_pn op
WHERE op.outline_pn = p_partno;

SELECT COUNT(*) INTO sub_exits
FROM sub_pn sp
WHERE sp.sub_assy_pn = p_partno;


RETURN (out_exists + sub_exists);
END check_parts;