SQL - subqueries with two data sets

Question

I have two input data tables, one for 2021 and one for 2022. And I would like to have one column per year (unitédoeuvre21 for 2021, and unitédoeuvre22 for 2022) based on certain conditions. But I can't do it. I would like something like this :

user_id  app   object unitédoeuvre21 unitédoeuvre22
abc      Dapp   sho        256           245

Here is my code:

SELECT
COALESCE (ac2021.user_id,ac2021.user_id) as user_id,
COALESCE (ac2021.app,ac2022.app) as app,
COALESCE (ac2021.object,ac2022.object) as object
FROM
(SELECT app, user_id, object, unitédoeuvre21, unitédoeuvre22
FROM 
(SELECT ac2021.app
, user_id
, object
, SUM(qt) * CASE object
      WHEN 'sho' THEN 14
      WHEN 'Coa' THEN 37
      ELSE NULL
END  as unitédoeuvre21
FROM achat2021
WHERE app IN (
  'sorena'
 ,'ReqStat'
 ,'Dapp'
)
GROUP BY 
  app
, object
, user_id) as ac2021
FULL JOIN
(SELECT ac2022.app
, user_id
, object
, SUM(qt) * CASE object
      WHEN 'sho'     THEN 14
      WHEN 'Coa' THEN 37
      ELSE NULL
END  as unitédoeuvre22
FROM achat2022
WHERE app IN (
  'sorena'
 ,'ReqStat'
 ,'Dapp'
)
GROUP BY 
  app
, object
, user_id) as ac2022
ON ((ac2021.user_id = ac2022.user_id) AND (ac2021.app = ac2022.app) AND (ac2021.object = ac2022.object))) as ac2122

I have the error :

missing FROM-clause entry for table "ac2021" line 8

Answer 1

I don't have anywhere to test it but something like this should work (I've split it into CTEs to show the logical steps):

WITH YEAR_2021 AS (
    SELECT ac2021.app
    , user_id
    , object
    , SUM(qt) * CASE object
          WHEN 'sho' THEN 14
          WHEN 'Coa' THEN 37
          ELSE NULL
    END  as unitédoeuvre21
    , NULL AS unitédoeuvre22
    FROM achat2021
    WHERE app IN (
      'sorena'
     ,'ReqStat'
     ,'Dapp'
    )
    GROUP BY USER_ID, APP, OBJECT
),
YEAR_2022 AS (
    SELECT ac2022.app
    , user_id
    , object
    , NULL AS unitédoeuvre21
    , SUM(qt) * CASE object
          WHEN 'sho'     THEN 14
          WHEN 'Coa' THEN 37
          ELSE NULL
    END  as unitédoeuvre22
    FROM achat2022
    WHERE app IN (
      'sorena'
     ,'ReqStat'
     ,'Dapp'
    )
    GROUP BY USER_ID, APP, OBJECT
),
BOTH_YEARS AS (
    SELECT USER_ID
    ,APP
    ,OBJECT
    ,unitédoeuvre21
    ,unitédoeuvre22
    FROM YEAR_2021
    UNION ALL
    SELECT USER_ID
    ,APP
    ,OBJECT
    ,unitédoeuvre21
    ,unitédoeuvre22
    FROM YEAR_2022
)
SELECT USER_ID
    ,APP
    ,OBJECT
    ,SUM(unitédoeuvre21) AS unitédoeuvre21
    ,SUM(unitédoeuvre22) AS unitédoeuvre22
FROM BOTH_YEARS
GROUP BY USER_ID, APP, OBJECT;