doubts with the GROUP BY and SUBSELECT

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 18 Oct 2007 1:19 pm

aí galera ... next ... I'm trying to run the code:
Code: Select all
SELECT mês, MESTOT, DECODE(MOD(ROWNUM, 2), 0, 0, 1) COR FROM
(
    SELECT TO_CHAR(S.SERAGE, 'MON-RR') mês, COUNT(1) MESTOT,
    (
        SELECT COUNT(1) MICOLOGIA
        FROM LABSERVICOS S2, LABSETORES ST2,
        LABSERVICOSSETOR SS2
        WHERE S2.SERID = SS2.SERID
        AND SS2.SETID = ST2.SETID
        AND TO_CHAR(S2.SERAGE, 'MON-RR') = TO_CHAR(S.SERAGE, 'MON-RR')
        AND ST2.SETNOM = 'MICOLOGIA'
    ) MICOLOGIA
    FROM LABSERVICOS S
    WHERE S.SERAGE BETWEEN (SYSDATE-600000) AND (SYSDATE+60000)
    GROUP BY TO_CHAR(S.SERAGE, 'MON-RR')
    ORDER BY mês
);
ta giving the following error: \"ORA-00979: not a GROUP BY expression\" and pointing to the \"S.\" inside the SUBSELECT SERAGE .... anyone know if there are any syntax errors or logic here???

Thank you all! =) vlw!
Renan Orati
Location: São José do Rio Preto - SP

Poston Fri, 19 Oct 2007 7:14 am

I didn't do the test by ta muito corrido aqui in trampo ... Friday seems q td quer give Dick one more try to see that the vê + right
Code: Select all
SELECT mês, MESTOT, DECODE(MOD(ROWNUM, 2), 0, 0, 1) COR FROM
(
    SELECT TO_CHAR(S.SERAGE, 'MON-RR') mês,MICOLOGIA.MICOLOGIA, COUNT(1) MESTOT
    FROM LABSERVICOS S ,
        (
        SELECT COUNT(1) MICOLOGIA
        FROM LABSERVICOS S2, LABSETORES ST2,
        LABSERVICOSSETOR SS2
        WHERE S2.SERID = SS2.SERID
        AND SS2.SETID = ST2.SETID
        AND TO_CHAR(S2.SERAGE, 'MON-RR') = TO_CHAR(S.SERAGE, 'MON-RR')
        AND ST2.SETNOM = 'MICOLOGIA'
    ) MICOLOGIA
    WHERE S.SERAGE BETWEEN (SYSDATE-600000) AND (SYSDATE+60000)
    GROUP BY TO_CHAR(S.SERAGE, 'MON-RR') ,MICOLOGIA.MICOLOGIA
    ORDER BY mês
);
rodfbar
Location: Batatais - SP

Poston Fri, 19 Oct 2007 4:23 pm

Our kara ... well thought out ... pity it didn't work ... because in the second select ... I'm doing a join with the table \"LABSERVICOS\" and he does not accept I do this junction in the \"FROM\" you know? ... but thanks for the tip ... I'm sure I'm going to use this kind of logic you presented! =) I solved the problem with a gambiarrinha aqui! lol I had to create a \"VIEW\" to select the months and generate the \"GROUP BY\" then he accepts ... with the same syntax error ... the only difference is that instead of me using \"TO_CHAR (S2.SERAGE, ' MON-RR ') \"at the junction and in the group by ... I used the field of view ... which returns the same value ... and oracle took ... like this:
Code: Select all
SELECT MESANO, MICOLOGIA, DECODE(MOD(ROWNUM, 2), 0, 0, 1) COR FROM
(
    SELECT VST.MESANO,
    (
        SELECT COUNT(1)
        FROM LABSERVICOS S2, LABSETORES ST2,
        LABSERVICOSSETOR SS2
        WHERE S2.SERID = SS2.SERID
        AND SS2.SETID = ST2.SETID
        AND TO_CHAR(S2.SERAGE, 'MON-RR') = VST.MESANO
        AND ST2.SETNOM = 'MICOLOGIA'
    ) MICOLOGIA
    FROM LABSERVICOS S, V_SERVICOSTEMPO VST
    WHERE S.SERAGE BETWEEN (SYSDATE-365) AND (SYSDATE)
    AND TO_CHAR(S.SERAGE, 'MON-RR') = VST.MESANO
    GROUP BY VST.MESANO
    ORDER BY TO_DATE(VST.MESANO, 'MM-RR')
);
vlw ai help! t +
Renan Orati
Location: São José do Rio Preto - SP



Return to SQL

Who is online

Users browsing this forum: Bing [Bot] and 2 guests