IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 1 reply
  • Latest Post - ‏2019-09-02T06:16:32Z by B.Hauser
renaneds
renaneds
1 Post

Pinned topic Help me with Sql Code

‏2019-09-01T16:52:30Z | db2 sql union unionall

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 keeps listing 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

  • B.Hauser
    B.Hauser
    320 Posts

    Re: Help me with Sql Code

    ‏2019-09-02T06:16:32Z  

    IMHO you do not need any UNION clause, nor a nested sub-Select.

    Try the following SELECT statement whether it returns what you want:
    Select COMPANY, Min(Case When TIPOCATEGORIA = 'A' Then 'SALE'
                             End As TYPOCATEGORY),
                    Min(Case When TIPOCATEGORIA = 'F' Then 'RETURN'
                             End As TYPOCATEGORY)
       From (Select Distinct COMPANY, TIPOCHATEGORY
               From DBA.NOTAS_ENTRADA_IDAIDA
               Where     NOTES_ENTRADA_SAIDA.TIPOCATEGORIA in ('A', 'F')
                     And NOTES_ENGTH_OUT_DATE.DTMOVEMENT > '2019-08-05') Type
       Group By Company
    

    BTW ONDE is not allowed in a SELECT statement it must be WHERE

     

    Birgitta