0
\$\begingroup\$

I believe the provided code can be rewritten shorter. Now one block is basically copy-pasted. In reality I have 6 such blocks.

This would be the data:

WITH
x(id, yr, more_info) AS
    (SELECT 1, 2020, 'info1' FROM DUAL UNION 
     SELECT 2, 2021, 'info2' FROM DUAL UNION 
     SELECT 3, 2021, 'info3' FROM DUAL UNION 
     SELECT 4, 2020, 'info4' FROM DUAL )
,
n(n_id, n_id2, n_year) AS
    (SELECT 1, 3, 2021 FROM DUAL UNION 
     SELECT 2, 4, 2021 FROM DUAL )

Desired result - complemented table x with up-to-date column yr values and info taken from previous year:
enter image description here

Current code:

WITH
x(id, yr, more_info) AS
    (SELECT 1, 2020, 'info1' FROM DUAL UNION 
     SELECT 2, 2021, 'info2' FROM DUAL UNION 
     SELECT 3, 2021, 'info3' FROM DUAL UNION 
     SELECT 4, 2020, 'info4' FROM DUAL )
,
n(n_id, n_id2, n_year) AS
    (SELECT 1, 3, 2021 FROM DUAL UNION 
     SELECT 2, 4, 2021 FROM DUAL )
     
SELECT * FROM x

-- ========>  BLOCK_1 for column n_id  <=========
UNION
SELECT n_id, n_year, more_info 
FROM (SELECT * 
      FROM x
      WHERE (id, yr) IN (SELECT id, MAX(yr)
                         FROM x
                         GROUP BY id) ) t1,
     (SELECT n_id, n_year
      FROM n
      WHERE (n_id, n_year) NOT IN (SELECT id, yr
                                   FROM x) ) t2
WHERE t1.id = t2.n_id

-- ========>  BLOCK_2 for column n_id2  <========
UNION
SELECT n_id2, n_year, more_info 
FROM (SELECT * 
      FROM x
      WHERE (id, yr) IN (SELECT id, MAX(yr)
                         FROM x
                         GROUP BY id) ) t1,
     (SELECT n_id2, n_year
      FROM n
      WHERE (n_id2, n_year) NOT IN (SELECT id, yr
                                   FROM x) ) t2
WHERE t1.id = t2.n_id2
\$\endgroup\$
1
  • 1
    \$\begingroup\$ The current question title, which states your concerns about the code, applies to too many questions on this site to be useful. The site standard is for the title to simply state the task accomplished by the code. Please see How do I ask a good question?. \$\endgroup\$
    – BCdotWEB
    Commented Feb 2, 2021 at 17:02

1 Answer 1

0
\$\begingroup\$

Found a possibility employing UNPIVOT

WITH
x(id, yr, more_info) AS
    (SELECT 1, 2020, 'info1' FROM DUAL UNION 
     SELECT 2, 2021, 'info2' FROM DUAL UNION 
     SELECT 3, 2021, 'info3' FROM DUAL UNION 
     SELECT 4, 2020, 'info4' FROM DUAL )
,
n(n_id, n_id2, n_year) AS
    (SELECT 1, 3, 2021 FROM DUAL UNION 
     SELECT 2, 4, 2021 FROM DUAL )
     
SELECT * FROM x
UNION
SELECT id2, n_year, more_info 
FROM (SELECT * 
      FROM x
      WHERE (id, yr) IN (SELECT id, MAX(yr)
                         FROM x
                         GROUP BY id) ) t1,
     (SELECT * FROM n
      UNPIVOT(
          id2
          FOR id_type
          IN (n_id, n_id2) ) ) t2
WHERE t1.id = t2.id2
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.