Toad World® Forums

Help me abou Union all on DB2

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!

Hi,
If you want to eliminate duplicates, you just have to use UNION, not UNION ALL,

Difference between UNION and UNION ALL is simple,

  • UNION sort data before merging to eliminate duplicates but a little bit slower
  • UNION ALL not sorting data, faster but possible duplicates.

Your case is different, i think you want to merge two records...

FULL JOIN may be more appropriate..

Regards,

Franck