GARANTIR DESCONTO

Fórum problema com group by usando subquery #28749

17/03/2007

0

Oracle

TENTEI AGRUPAR ASSIM MAS NAO DEU CERTO, O QUE FAÇO.

SELECT TO_CHAR(A.DATA,´MM-YY´) AS ´DATA´,
(SELECT SUM(B.ATR4) / 1000 FROM NIVEL3IMP B, TAB_NIVEL3IMP C WHERE
B.NIVEL3 = C.NIVEL3 AND C.DESCRICAO = ´AEREA´ AND TO_CHAR(B.DATA,´MM-YY´) = TO_CHAR(A.DATA,´MM-YY´)
) AS ´AEREA´,
(SELECT SUM(X.ATR4) / 1000 FROM NIVEL3IMP X, TAB_NIVEL3IMP Y WHERE
X.NIVEL3 = Y.NIVEL3 AND Y.DESCRICAO = ´MARITIMA´ AND TO_CHAR(X.DATA,´MM-YY´) = TO_CHAR(A.DATA,´MM-YY´)
) AS ´MARITIMA´
FROM NIVEL3IMP A
WHERE TO_DATE(TO_CHAR(A.DATA,´MM-YY´), ´MM-YY´) BETWEEN TO_DATE(´@1@1´,´MM-YY´) AND TO_DATE(´@2@1´,´MM-YY´)
GROUP BY TO_CHAR(A.DATA,´MM-YY´)


Stgmta

Stgmta

Responder

Posts

19/03/2007

Motta

Tente sem o group by


Responder

Gostei + 0

19/03/2007

Rosterne

Ou tente algo tipo:

SELECT TO_CHAR(A.DATA,´MM-YY´) AS ´DATA´, SUM(B1.ATR4) / 1000 AS ´AEREA´, SUM(X1.ATR4) / 1000 AS ´MARITIMA´
FROM NIVEL3IMP A
(SELECT B.ATR4, B.DATA FROM NIVEL3IMP B, TAB_NIVEL3IMP C WHERE B.NIVEL3 = C.NIVEL3 and C.DESCRICAO = ´AEREA´) b1,
(SELECT x.ATR4, x.DATA FROM NIVEL3IMP X, TAB_NIVEL3IMP Y WHERE X.NIVEL3 = Y.NIVEL3 AND Y.DESCRICAO = ´MARITIMA´) x1
WHERE TO_DATE(TO_CHAR(A.DATA,´MM-YY´), ´MM-YY´) BETWEEN TO_DATE(´@1@1´,´MM-YY´) AND TO_DATE(´@2@1´,´MM-YY´)
and TO_CHAR(B1.DATA,´MM-YY´) = TO_CHAR(A.DATA,´MM-YY´)
and TO_CHAR(X1.DATA,´MM-YY´) = TO_CHAR(A.DATA,´MM-YY´)
GROUP BY TO_CHAR(A.DATA,´MM-YY´);

Abraço.


Responder

Gostei + 0

19/03/2007

Stgmta

Ou tente algo tipo: SELECT TO_CHAR(A.DATA,´MM-YY´) AS ´DATA´, SUM(B1.ATR4) / 1000 AS ´AEREA´, SUM(X1.ATR4) / 1000 AS ´MARITIMA´ FROM NIVEL3IMP A (SELECT B.ATR4, B.DATA FROM NIVEL3IMP B, TAB_NIVEL3IMP C WHERE B.NIVEL3 = C.NIVEL3 and C.DESCRICAO = ´AEREA´) b1, (SELECT x.ATR4, x.DATA FROM NIVEL3IMP X, TAB_NIVEL3IMP Y WHERE X.NIVEL3 = Y.NIVEL3 AND Y.DESCRICAO = ´MARITIMA´) x1 WHERE TO_DATE(TO_CHAR(A.DATA,´MM-YY´), ´MM-YY´) BETWEEN TO_DATE(´@1@1´,´MM-YY´) AND TO_DATE(´@2@1´,´MM-YY´) and TO_CHAR(B1.DATA,´MM-YY´) = TO_CHAR(A.DATA,´MM-YY´) and TO_CHAR(X1.DATA,´MM-YY´) = TO_CHAR(A.DATA,´MM-YY´) GROUP BY TO_CHAR(A.DATA,´MM-YY´); isto deu o seguinte erro: sql command not properly ended Abraço.



Responder

Gostei + 0

19/03/2007

Rosterne

tente:

SELECT TO_CHAR(A.DATA,´MM-YY´) AS ´DATA´, SUM(B1.ATR4) / 1000 AS ´AEREA´, SUM(X1.ATR4) / 1000 AS ´MARITIMA´
FROM NIVEL3IMP A,
(SELECT B.ATR4, B.DATA FROM NIVEL3IMP B, TAB_NIVEL3IMP C WHERE B.NIVEL3 = C.NIVEL3 and C.DESCRICAO = ´AEREA´) b1,
(SELECT x.ATR4, x.DATA FROM NIVEL3IMP X, TAB_NIVEL3IMP Y WHERE X.NIVEL3 = Y.NIVEL3 AND Y.DESCRICAO = ´MARITIMA´) x1
WHERE TO_DATE(TO_CHAR(A.DATA,´MM-YY´), ´MM-YY´) BETWEEN TO_DATE(´@1@1´,´MM-YY´) AND TO_DATE(´@2@1´,´MM-YY´)
and TO_CHAR(B1.DATA,´MM-YY´) = TO_CHAR(A.DATA,´MM-YY´)
and TO_CHAR(X1.DATA,´MM-YY´) = TO_CHAR(A.DATA,´MM-YY´)
GROUP BY TO_CHAR(A.DATA,´MM-YY´);


Responder

Gostei + 0

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar