Subconsulta mais adequada
02/03/2016
0
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?
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?
Julio Pereira
Curtir tópico
+ 0
Responder
Posts
02/03/2016
Marcos P
Sempre que postar código, utilize a tag <Inserir Código>, isso ajuda o entendimento do problema...
Tenta assim...
Tenta assim...
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
Responder
Clique aqui para fazer login e interagir na Comunidade :)
