Using a column value in a calculation for a nested SQL query

Question

I have the following SQL query:

SELECT
   (SELECT CATEGORY_NAME FROM tableA WHERE NUM_CRITERIA != 0) AS "Name",
   (numYes / (criteriaCount * numParts - numNA)) / numParts AS "Progress"
FROM (
   SELECT
      (SELECT NUM_CRITERIA FROM tableA WHERE NUM_CRITERIA != 0) AS criteriaCount,
      (SELECT COUNT(*) FROM tableB WHERE RESPONSE = 'Yes' AND ASSEMBLY_ID = 1) AS numYes,
      (SELECT COUNT(*) FROM tableB WHERE RESPONSE = 'N/A' AND ASSEMBLY_ID = 1) AS numNA,
      (SELECT COUNT(*) FROM tableC WHERE ASSEMBLY_ID = 1) AS numParts
   FROM DUAL
)

The goal of this query is to select columns from various tables and use the values of those columns to calculate the values of the "Progress" column. I need the "Progress" column to do the calculation in the code for each value of the criteriaCount column. The code compiles fine in oracle apex but when it is ran I get an ORA-01427 error because the criteriaCount query returns multiple rows.

Any help would be greatly appreciated

Answer 1

Since the query for TableA can return multiple rows (and you want that) it cannot be placed as a scalar subquery together with the other ones.

You'll need to query the TableA directly and join the result with the other subqueries.

For example:

SELECT
 a.NUM_CRITERIA, 
 a.category_name as name,
 (b.numYes / (a.NUM_CRITERIA * b.numParts - b.numNA)) / b.numParts AS "Progress"
FROM tableA a
cross join (
  SELECT
    (SELECT COUNT(*) FROM tableB WHERE RESPONSE = 'Yes' AND ASSEMBLY_ID = 1) AS numYes,
    (SELECT COUNT(*) FROM tableB WHERE RESPONSE = 'N/A' AND ASSEMBLY_ID = 1) AS numNA,
    (SELECT COUNT(*) FROM tableC WHERE ASSEMBLY_ID = 1) AS numParts
  FROM DUAL
) b
WHERE a.NUM_CRITERIA <> 0