Help me abou DB2 Sql Code

[renaneduardoccb]

Good afternoon,

I need to make a select that makes full union between two records.

I used UNION ALL, but when it generates my SQL result, it lists the duplicate result and what I want is to merge both into one line.

For example:

In the first statement, I have:

Company ID, grade category (sale)

In the second statement, I have:

Company ID, Grade Category (Return)



When I generate it looks like this:

Company - Category

1 - SALE - RETURN

1 - RETURN - SALE



Being that, I wanted to unite like this:

Company - Category - Category

1 - SALE - RETURN

- "Below is the SQL I made so far" ***

SELECT

COMPANY,

CASE WHEN TIPOCATEGORIA = 'A' THEN

'SALE'

ELSE 'RETURN'

END AS TYPOCATEGORY,

CASE WHEN TIPOCATEGORIA = 'F' THEN

'SALE'

ELSE 'RETURN'

END AS TYPOCATEGORY

FROM

(

SELECT

COMPANY,

'A' AS TYPOCATEGORY

FROM

(

SELECT

COMPANY,

TYPOCHATEGORY

FROM

DBA.NOTAS_ENTRADA_IDAIDA

ONDE

NOTES_ENTRADA_SAIDA.TIPOCATEGORIA = 'A' AND

NOTES_ENGTH_OUT_DATE.DTMOVEMENT> '2019-08-05'

GROUP BY

COMPANY,

TYPOCHATEGORY

) TYPE

GROUP BY

TYPE.Company,

TYPE.TIPOCATEGORY



UNION ALL



SELECT

COMPANY,

'F' AS TYPOCATEGORY

FROM

(

SELECT

COMPANY,

TYPOCHATEGORY

FROM

DBA.NOTAS_ENTRADA_IDAIDA

ONDE

NOTES_ENTRADA_SAIDA.TIPOCATEGORIA = 'F' AND

NOTES_ENGTH_OUT_DATE.DTMOVEMENT> '2019-08-05'

GROUP BY

COMPANY,

TYPOCHATEGORY

) AS KINDS

GROUP BY

TYPE.Company,

TYPE.TIPOCATEGORY

) ALL

GROUP BY

COMPANY,

TYPOCHATEGORY

Please, show me the correct form. Thank you so much!

 

[johnherman]

UNION will not return duplicate records.
UNION ALL returns everything, including duplicates, triplicates, etc.
Hope this helps.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[MarcLodge]

Try:

SELECT S.COMPANY, S.CATEGORY, R.CATEGORY
  FROM TABLE S
      ,TABLE R
WHERE S.CATEGORY = 'SALE'
  AND S.COMPANY  = R.COMPANY
  AND R.CATEGORY = 'RETURN'

This would take two lines and combine into 1. The problem with this is that it will only give a line if the Company has both sales and return data. If that is not what is required, then you will need an outer join .

Hope I have understood the problem and that this helps.