I need the identical lines to merge

All we need is an easy explanation of the problem, so here it is.

Previous question: Return row with values if nothing fits the conditions

I almost got this working.
The problem that I have is that this variant of query does not merge the line from the SELECT with the one in the UNION if they are the same:

SELECT CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM 
FROM FGE50LM0TV.GESUPD                              
WHERE CNFLIG = '2'                                                                          
AND CODPRO IN (SELECT VALINV FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59)                                 
GROUP BY CODACT, CODPRO, VALPRO 
UNION (SELECT VALINV, 0, 0 FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59) ;

This is what SVINVD looks like:

This is what GESUPD looks like:

No line for "PRODUIT5" here.

So in consequence I get a doubled line:

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The UNION operator eliminates duplicate rows. It means that two rows will merge only if they are completely identical. Row

will not merge with row

because the values in the last column differ.

Anyway, if the goal is to complete the set returned by

SELECT CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM
FROM FGE50LM0TV.GESUPD
WHERE CNFLIG = '2'
AND CODPRO IN (SELECT VALINV FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59) 

with missing values found in

SELECT VALINV FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59

then there are a few approaches to this.

1. UNION + EXCEPT

SELECT CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM
FROM FGE50LM0TV.GESUPD
WHERE CNFLIG = '2'
AND CODPRO IN (SELECT VALINV FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59)
UNION
(
SELECT VALINV, 0, 0 FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59
EXCEPT
SELECT CODPRO, 0, 0 FROM FGE50LM0TV.GESUPD WHERE CNFLIG = '2'
)

There are repetitive bits of logic here, like the CNFLIG = '2' – an aspect which I personally prefer to avoid in my queries if possible. Other than that, this seems like more or less straightforward approach to me.

2. LEFT JOIN

Use your query as a derived table and outer-join it to FGE50LM0TV.SVINVD. Substitute 0 for missing rows’ VALPRO and SUM. Like this:

SELECT
  s.VALINV AS CODPRO, COALESCE(g.VALPRO, 0) AS VALPRO, COALESCE(g.SUM, 0) AS SUM
FROM
  FGE50LM0TV.SVINVD AS s
  LEFT JOIN
  (
    SELECT
      CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM
    FROM
      FGE50LM0TV.GESUPD
    WHERE
      CNFLIG = '2'
  ) AS g ON s.VALINV = g.CODPRO
WHERE
  s.NUMINV = 59
;

3. LEFT JOIN, Alternative Option

You can also try outer-joining FGE50LM0TV.GESUPD to FGE50LM0TV.SVINVD directly, and then apply the grouping:

SELECT
  s.VALINV AS CODPRO, COALESCE(g.VALPRO, 0) AS VALPRO, COALESCE(SUM(g.UVCLIV), 0) AS SUM
FROM
  FGE50LM0TV.SVINVD AS s
  LEFT JOIN FGE50LM0TV.GESUPD AS g ON s.VALINV = g.CODPRO AND g.CNFLIG = '2'
WHERE
  s.NUMINV = 59
GROUP BY
  s.VALINV, g.VALPRO
;

Note that the CNFLIG = '2' is moved from WHERE to the ON clause. This is so as to avoid eliminating non-matching GESUPD entries. The WHERE clause applies to the entire joined set, and where GESUPD has no match for SVINVD, the CNFLIG = '2' would completely exclude that row from the output, contrary to what you are trying to achieve. In the ON clause, however, the condition would apply to GESUPD only, and SVINVD rows that have no match would still be returned.

While generally looking simpler than the other LEFT JOIN solution, this option may not necessarily be as fast. You will have to test both to compare the performance for yourself. Naturally, that goes for all three options.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0