Array
(
)

Subconsulta mais adequada

Julio Pereira
   - 02 mar 2016

Olá. Preciso de vossa ajuda.
Tenho o seguinte:
SELECT PS.CD_PER_SAFRA AS SAFRA, SUM(U3.QT_AREA_PROD) AS AREA
FROM UPNIVEL3 U3
JOIN PERIODOSAFRA PS ON (PS.ID_PERIODOSAFRA = U3.ID_PERIODOSAFRA)
JOIN SAFRA SF ON (SF.ID_SAFRA = PS.ID_SAFRA)
JOIN UPNIVEL2 U2 ON (U2.ID_UPNIVEL2 = U3.ID_UPNIVEL2)
JOIN UPNIVEL1 U1 ON (U1.ID_UPNIVEL1 = U2.ID_UPNIVEL1)
WHERE PS.CD_PER_SAFRA = '201601'
GROUP BY PS.CD_PER_SAFRA
UNION
SELECT PS.CD_PER_SAFRA AS SAFRA, SUM(CASE WHEN OC.FG_SITUACAO = 'F' THEN LC.QT_AREA_COLH ELSE 0 END) AS AREA_COLHIDA
FROM ORDCOLHEITA_LC LC
left outer JOIN UPNIVEL3 U3 ON (U3.ID_UPNIVEL3 = LC.ID_UPNIVEL3)
JOIN PERIODOSAFRA PS ON (PS.ID_PERIODOSAFRA = U3.ID_PERIODOSAFRA)
JOIN SAFRA SF ON (SF.ID_SAFRA = PS.ID_SAFRA)
JOIN UPNIVEL2 U2 ON (U2.ID_UPNIVEL2 = U3.ID_UPNIVEL2)
JOIN UPNIVEL1 U1 ON (U1.ID_UPNIVEL1 = U2.ID_UPNIVEL1)
JOIN ORDCOLHEITA OC ON (OC.ID_ORDCOLHEITA = LC.ID_ORDCOLHEITA)
WHERE PS.CD_PER_SAFRA = '201601'
GROUP BY PS.CD_PER_SAFRA
O resultado é mostrado em 2 linhas:
SAFRA AREA
201601 37473.37 [Essa linha contém a Área Total Colhida]
201601 43905.76 [Essa linha contém a Área Total Plantada]
Como fazer p/ mostrar em uma única linha o resultado da consulta?

Marcos P
   - 02 mar 2016

Sempre que postar código, utilize a tag <Inserir Código>, isso ajuda o entendimento do problema...

Tenta assim...

#Código
SELECT (SELECT PS.CD_PER_SAFRA
FROM UPNIVEL3 U3
JOIN PERIODOSAFRA PS ON (PS.ID_PERIODOSAFRA = U3.ID_PERIODOSAFRA)
JOIN SAFRA SF ON (SF.ID_SAFRA = PS.ID_SAFRA)
JOIN UPNIVEL2 U2 ON (U2.ID_UPNIVEL2 = U3.ID_UPNIVEL2)
JOIN UPNIVEL1 U1 ON (U1.ID_UPNIVEL1 = U2.ID_UPNIVEL1)
WHERE PS.CD_PER_SAFRA = '201601'
GROUP BY PS.CD_PER_SAFRA) AS SAFRA1,
(SELECT SUM(U3.QT_AREA_PROD)
FROM UPNIVEL3 U3
JOIN PERIODOSAFRA PS ON (PS.ID_PERIODOSAFRA = U3.ID_PERIODOSAFRA)
JOIN SAFRA SF ON (SF.ID_SAFRA = PS.ID_SAFRA)
JOIN UPNIVEL2 U2 ON (U2.ID_UPNIVEL2 = U3.ID_UPNIVEL2)
JOIN UPNIVEL1 U1 ON (U1.ID_UPNIVEL1 = U2.ID_UPNIVEL1)
WHERE PS.CD_PER_SAFRA = '201601'
GROUP BY PS.CD_PER_SAFRA) AS AREA,
(SELECT PS.CD_PER_SAFRA
FROM ORDCOLHEITA_LC LC
left outer JOIN UPNIVEL3 U3 ON (U3.ID_UPNIVEL3 = LC.ID_UPNIVEL3)
JOIN PERIODOSAFRA PS ON (PS.ID_PERIODOSAFRA = U3.ID_PERIODOSAFRA)
JOIN SAFRA SF ON (SF.ID_SAFRA = PS.ID_SAFRA)
JOIN UPNIVEL2 U2 ON (U2.ID_UPNIVEL2 = U3.ID_UPNIVEL2)
JOIN UPNIVEL1 U1 ON (U1.ID_UPNIVEL1 = U2.ID_UPNIVEL1)
JOIN ORDCOLHEITA OC ON (OC.ID_ORDCOLHEITA = LC.ID_ORDCOLHEITA)
WHERE PS.CD_PER_SAFRA = '201601'
GROUP BY PS.CD_PER_SAFRA) AS SAFRA2,
(SELECT SUM(CASE WHEN OC.FG_SITUACAO = 'F' THEN LC.QT_AREA_COLH ELSE 0 END)
FROM ORDCOLHEITA_LC LC
left outer JOIN UPNIVEL3 U3 ON (U3.ID_UPNIVEL3 = LC.ID_UPNIVEL3)
JOIN PERIODOSAFRA PS ON (PS.ID_PERIODOSAFRA = U3.ID_PERIODOSAFRA)
JOIN SAFRA SF ON (SF.ID_SAFRA = PS.ID_SAFRA)
JOIN UPNIVEL2 U2 ON (U2.ID_UPNIVEL2 = U3.ID_UPNIVEL2)
JOIN UPNIVEL1 U1 ON (U1.ID_UPNIVEL1 = U2.ID_UPNIVEL1)
JOIN ORDCOLHEITA OC ON (OC.ID_ORDCOLHEITA = LC.ID_ORDCOLHEITA)
WHERE PS.CD_PER_SAFRA = '201601'
GROUP BY PS.CD_PER_SAFRA) AS AREA_COLHIDA