0

I have his query that works fine, but I need it to return a line when nothing is found to suit the conditions:

SELECT D.CODPRO, D.VALPRO, COALESCE(SUM(d.UVCLIV), 0)  AS SUM 
FROM FGE50LM0TV.GESUPE E LEFT JOIN FGE50LM0TV.GESUPD D   
ON E.NUMSUP=D.NUMSUP AND E.SNUSUP=D.SNUSUP               
WHERE E.CODACT = '001'                                         
AND D.CODPRO <> ''                                       
AND E.CIRPIC = 'HUB'                                     
AND E.ETASUP IN ('30','40','60')                         
AND D.CNFLIG = '2'                                       
AND E.TOPMNQ <> '1'                                      
AND D.CODPRO IN ('TST UOP 1')                                 
GROUP BY D.CODACT, D.CODPRO, D.VALPRO

Maybe the question is not that clear. Let me simplify it:

I want the following query to return the CODPRO, VALPRO, 0 as SUM of UVCLIV when CNFLIG is <> of '2':

SELECT CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM 
FROM FGE50LM0TV.GESUPD                              
WHERE CNFLIG = '2'                                                                          
AND CODPRO IN ('TST UOP 1')                                 
GROUP BY CODACT, CODPRO, VALPRO ;

This is what I have for this product now in the table:

enter image description here

I use this query to insert a line of PRODUCT, VARIANT, QUANTITY into a file (H) by making a sum of all lines with that PRODUCT/VARIANT from another file (D). I have all those conditions that I wrote, and I want to receive a line with the PRODUCT, VARIANT, 0 as SUM for the situation when the SQL does not find anything according to my conditions.

I am not using MySQL workbench. I am on an IBM Emulator Utility v 14.14

Paul White
  • 94,921
  • 30
  • 437
  • 687
Oliviu Vais
  • 33
  • 3

2 Answers2

1

My understanding is that

  • for the subset where CODPRO IN ('TST UOP 1'), the FGE50LM0TV.GESUPD dataset has all the combinations of CODPRO, VALPRO that you want;
  • not all of the combinations match the additional condition of CNFLIG = '2';
  • you would like to obtain the sums for all those combinations, including the ones that do not have CNFLIG = '2'.

If my understanding is correct, then you could just remove the CNFLIG = '2' condition from the WHERE clause and use it inside SUM as part of a CASE expression, like this:

SELECT
  CODPRO
, VALPRO
, COALESCE(SUM(CASE WHEN CNFLIG = '2' THEN UVCLIV END), 0)  AS SUM
FROM
  FGE50LM0TV.GESUPD
WHERE
  CODPRO IN ('TST UOP 1')                            
GROUP BY
  CODACT
, CODPRO
, VALPRO
;

This way you are not simply getting the sum of UVCLIV. You are including UVCLIV in the sum only if CNFLIG is '2'. Otherwise the expression evaluates to null and the SUM function omits it. If a particular row group has no single row matching that condition, the SUM will return null, but your COALESCE is there to turn it into a 0.

Andriy M
  • 23,261
  • 6
  • 60
  • 103
0

I hope i get your question right. Just reverse the where clauses one by one like below. This should work:

SELECT D.CODPRO, D.VALPRO, COALESCE(SUM(d.UVCLIV), 0)  AS SUM 
FROM FGE50LM0TV.GESUPE E LEFT JOIN FGE50LM0TV.GESUPD D   
ON E.NUMSUP=D.NUMSUP AND E.SNUSUP=D.SNUSUP               
WHERE E.CODACT <> '001'                                         
AND D.CODPRO = ''                                       
AND E.CIRPIC <> 'HUB'                                     
AND E.ETASUP NOT IN ('30','40','60')                         
AND D.CNFLIG <> '2'                                       
AND E.TOPMNQ = '1'                                      
AND D.CODPRO NOT IN ('TST UOP 1')                                 
GROUP BY D.CODACT, D.CODPRO, D.VALPRO
postgresnewbie
  • 127
  • 9