Difference between two queries in SQL (Oracle Apex Items)-CodePudding

I have the two following queries in different items in an APEX application:

ITEM 1:

SELECT SUM(t.gems)
  FROM (SELECT gems
          FROM tasks
         UNION ALL
        SELECT gems
          FROM quests
         UNION ALL
        SELECT gems
          FROM daily_quests
         ) t

ITEM 2:

SELECT SUM(price) FROM items

They both return a number correctly, but I want to subtract them in one statement. I tried to use

SELECT TO_NUMBER(ITEM1)-TO_NUMBER(ITEM2) FROM DUAL

but it didn't work.

Do you have any suggestions? I am a bit new to APEX and SQL.

Thank you in advance.

CodePudding user response：

Use a CTE (Common table expression) for each of the queries, then CROSS JOIN them.

WITH t_gems (sum_gems) AS
(
SELECT SUM(t.gems)
  FROM (SELECT gems
          FROM tasks
         UNION ALL
        SELECT gems
          FROM quests
         UNION ALL
        SELECT gems
          FROM daily_quests
         ) t
), t_items (sum_price) AS
(
  SELECT SUM(price) FROM items
)
SELECT t_gems.sum_gems - t_item.sum_price 
  FROM t_gems CROSS JOIN t_items

CodePudding user response：

Assuming each query produces a column named gems you can do:

with
a as (
  -- query 1 here
),
b as (
  -- query 2 here
)
select a.gems - b.gems as diff
from a
cross join b