renaneduardoccb
Programmer
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!
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!