Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help me abou DB2 Sql Code

Status
Not open for further replies.

renaneduardoccb

Programmer
Sep 1, 2019
1
BR
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!
 
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


 
Try:
Code:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top