Union of two selects presenting online

Question

Below is a query performed for ORACLE database:

SELECT
ITOS.CD_NUMERO_OS,
ITOS.DESCRICAO_RECLA AS "DEFEITO APRESENTADO"

FROM GMITEMOS ITOS

INNER JOIN GMOSERVI OS ON OS.CD_NUMERO_OS = ITOS.CD_NUMERO_OS
INNER JOIN GMITEMOS IT ON IT.CD_NUMERO_OS = ITOS.CD_NUMERO_OS

WHERE OS.DT_OS BETWEEN TO_DATE(:data_os_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_os_final, 'DD/MM/YYYY')
AND OS.SITUACAO LIKE 'E'
AND IT.TIPO_OS LIKE '3'

UNION

SELECT DISTINCT
ITOS.CD_NUMERO_OS,
SL.DESCRICAO_SOLUC AS "SOLUCAO DO DEFEITO"

FROM GMITEMOS ITOS

INNER JOIN GMOSERVI OS ON OS.CD_NUMERO_OS = ITOS.CD_NUMERO_OS
INNER JOIN GMITEMOS IT ON IT.CD_NUMERO_OS = ITOS.CD_NUMERO_OS
INNER JOIN GMDETALH SL ON SL.CD_NUMERO_OS = ITOS.CD_NUMERO_OS

WHERE OS.DT_OS BETWEEN TO_DATE(:data_os_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_os_final, 'DD/MM/YYYY')
AND OS.SITUACAO LIKE 'E'
AND IT.TIPO_OS LIKE '3'

Below is a PRINT of how the information is being delivered on screen:

As we can observe, a DEFECT is presented and soon after a SOLUTION to the same number of OS (CD_NUMERO_OS). For example in the OS with CD_NUMERO_OS = 860, item 1 of the grid is a DEFECT and item 2 is a SOLUTION. I need to display DEFECT AND SOLUTION side by side, display in column format and not in rows according to the above print.

Any idea how I can display this result side by side???

Detail: A OS with CD_NUMERO_OS = 866 appears 3 times a DEFECT and 3 times a SOLUTION because the same OS has 3 different items.

Answer 1

Rewrite your select this way:

SELECT
    
ITOS.CD_NUMERO_OS,
ITOS.DESCRICAO_RECLA AS "DEFEITO APRESENTADO",
NVL(SL.DESCRICAO_SOLUC, 'NAO RESOLVIDO') AS "SOLUCAO DO DEFEITO"

FROM GMITEMOS ITOS,    
GMOSERVI OS,
GMITEMOS IT, 
GMDETALH SL

WHERE OS.DT_OS BETWEEN TO_DATE(:data_os_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_os_final, 'DD/MM/YYYY')
AND OS.CD_NUMERO_OS = ITOS.CD_NUMERO_OS
AND IT.CD_NUMERO_OS = ITOS.CD_NUMERO_OS
AND IT.ITEM = ITOS.ITEM /* faltou esse */
AND SL.CD_NUMERO_OS(+) = ITOS.CD_NUMERO_OS
AND OS.SITUACAO LIKE 'E'
AND IT.TIPO_OS LIKE '3'

I prefer to use equijoins, because I’ve seen cases (mainly in SQL mounted by the Entityframework of the .NET platform) that with Inner Join is slower, but then you can make a comparison and see which form is better.

See that to relate the table SL i used the (+) operator indicating a LEFT JOIN, because a defect may not have been solved

Answer 2

SELECT DISTINCT
    
ITOS.CD_NUMERO_OS,
ITOS.ITEM AS "ITEM DA OS",
ITOS.DESCRICAO_RECLA AS "DEFEITO APRESENTADO",
NVL(SL.DESCRICAO_SOLUC, 'NAO RESOLVIDO') AS "SOLUCAO DO DEFEITO"

FROM GMITEMOS ITOS,    
GMOSERVI OS,
GMITEMOS IT, 
GMDETALH SL

WHERE OS.DT_OS BETWEEN TO_DATE(:data_os_inicial, 'DD/MM/YYYY') AND TO_DATE(:data_os_final, 'DD/MM/YYYY')
AND OS.CD_NUMERO_OS = ITOS.CD_NUMERO_OS
AND IT.CD_NUMERO_OS = ITOS.CD_NUMERO_OS
AND SL.CD_NUMERO_OS(+) = ITOS.CD_NUMERO_OS
AND OS.SITUACAO LIKE 'E'
AND IT.TIPO_OS LIKE '3'

ORDER BY itos.cd_numero_os ASC, itos.item ASC

Follow the print of the result.