5 Replies Latest reply: Jun 29, 2012 7:02 AM by Mrucha RSS

    Subquery in Group By

    user12193344
      Gurus,

      I'm receiving the following error when I add a subquery in my group by clause.

      ORA-00933: SQL command not properly ended

      What am I doing wrong?

      select KAT.KATKEYI,
      VGB.VGBBEZC as template,
      DOK.DOKKEYI,
      STA.STABEZC AS STATEOFDOCUMENT,
      ABP.ABPBEZC AS WORKINGPLAN,
      DOK.DOKINFC AS DOCUMENTSINFO,
      NVL(ZPLBEZC, 'No TimePlan') AS TIMEPLAN,
      DOK.DOKOWNC AS RESPONSIBLE,
      stragg(DOK.DOKNLC) AS ASSIGMENTS,
      (select AFL.AFLFLDC from AFL inner join FTY on AFL.AFLFTYKEYI = FTY.FTYKEYI where FTY.FTYBEZC = 'Prod Instructions' and AFL.AFLARTKEYI = ART.ARTKEYI and AFL.AFLKAVKEYI = ART.ARTKAVKEYI) as prodinstructions
      from KAT,DOK,VGB,ABP,ZPL
      where ZPL.ZPLABPKEYI(+) = ABP.ABPKEYI
      AND KAT.KATVGBKEYI = VGB.VGBKEYI
      AND DOK.DOKKATKEYI = KAT.KATKEYI
      group by
      KAT.KATKEYI,
      VGB.VGBBEZC,
      DOK.DOKKEYI,
      STA.STABEZC,
      ABP.ABPBEZC,
      DOK.DOKINFC
      ZPL.ZPLBEZC,
      DOK.DOKOWNC,
      (select AFL.AFLFLDC from AFL inner join FTY on AFL.AFLFTYKEYI = FTY.FTYKEYI where FTY.FTYBEZC = 'Prod Instructions' and AFL.AFLARTKEYI = ART.ARTKEYI and AFL.AFLKAVKEYI = ART.ARTKAVKEYI) as prodinstructions;

      Thanks!
        • 1. Re: Subquery in Group By
          indra budiantho
          /* Formatted on 2012/06/29 05:51 (Formatter Plus v4.8.8) */
          SELECT   kat.katkeyi, vgb.vgbbezc AS TEMPLATE, dok.dokkeyi,
                   sta.stabezc AS stateofdocument, abp.abpbezc AS workingplan,
                   dok.dokinfc AS documentsinfo,
                   NVL (zplbezc, 'No TimePlan') AS timeplan, dok.dokownc AS responsible,
                   stragg (dok.doknlc) AS assigments,
                   (SELECT afl.aflfldc
                      FROM afl INNER JOIN fty
                           ON afl.aflftykeyi = fty.ftykeyi
                     WHERE fty.ftybezc = 'Prod Instructions'
                       AND afl.aflartkeyi = art.artkeyi
                       AND afl.aflkavkeyi = art.artkavkeyi) AS prodinstructions
              FROM kat, dok, vgb, abp, zpl
             WHERE zpl.zplabpkeyi(+) = abp.abpkeyi
               AND kat.katvgbkeyi = vgb.vgbkeyi
               AND dok.dokkatkeyi = kat.katkeyi
          GROUP BY kat.katkeyi,
                   vgb.vgbbezc,
                   dok.dokkeyi,
                   sta.stabezc,
                   abp.abpbezc,
                   dok.dokinfc,
                   zpl.zplbezc,
                   dok.dokownc,
                   (SELECT afl.aflfldc
                      FROM afl INNER JOIN fty ON afl.aflftykeyi = fty.ftykeyi
                     WHERE fty.ftybezc = 'Prod Instructions'
                       AND afl.aflartkeyi = art.artkeyi
                       AND afl.aflkavkeyi = art.artkavkeyi);
          • 2. Re: Subquery in Group By
            user12193344
            I tried your query and received ....

            ORA-22818: subquery expressions not allowed here
            • 3. Re: Subquery in Group By
              indra budiantho
              hi you can wrap, something like this, but i do not see your group function:
              select x.*, count(katkeyi) from(
              SELECT   kat.katkeyi, vgb.vgbbezc AS TEMPLATE, dok.dokkeyi,
                       sta.stabezc AS stateofdocument, abp.abpbezc AS workingplan,
                       dok.dokinfc AS documentsinfo,
                       NVL (zplbezc, 'No TimePlan') AS timeplan, dok.dokownc AS responsible,
                       stragg (dok.doknlc) AS assigments,
                       (SELECT afl.aflfldc
                          FROM afl INNER JOIN fty
                               ON afl.aflftykeyi = fty.ftykeyi
                         WHERE fty.ftybezc = 'Prod Instructions'
                           AND afl.aflartkeyi = art.artkeyi
                           AND afl.aflkavkeyi = art.artkavkeyi) AS prodinstructions
                  FROM kat, dok, vgb, abp, zpl
                 WHERE zpl.zplabpkeyi(+) = abp.abpkeyi
                   AND kat.katvgbkeyi = vgb.vgbkeyi
                   AND dok.dokkatkeyi = kat.katkeyi
              ) x    
              GROUP BY kat.katkeyi,
                       vgb.vgbbezc,
                       dok.dokkeyi,
                       sta.stabezc,
                       abp.abpbezc,
                       dok.dokinfc,
                       zpl.zplbezc,
                       dok.dokownc,
                       prodinstructions
              • 4. Re: Subquery in Group By
                Frank Kulash
                Hi,

                GROUP BY is doumented in the SQL language manual
                http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#sthref9438
                which says "The expressions can be of any form except scalar subquery expressions".

                In your original query, you were getting an error for trying to define an alias in the GROUP BY clause, so you never got the ORA-22818 error.

                To get the results you want, compjute the sclar sub-query within a sub-query, or do a join instead of a scalar sub-query.


                 

                I hope this answers your question.
                If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
                Explain, using specific examples, how you get those results from that data.
                Always say what version of Oracle you're using.
                See the forum FAQ {message:id=9360002}

                Edited by: Frank Kulash on Jun 28, 2012 8:36 PM
                • 5. Re: Subquery in Group By
                  Mrucha
                  You can not use alias and sub query with group by clause.
                  So remove below query
                  (select AFL.AFLFLDC from AFL inner join FTY on AFL.AFLFTYKEYI = FTY.FTYKEYI where FTY.FTYBEZC = 'Prod Instructions' and AFL.AFLARTKEYI = ART.ARTKEYI and AFL.AFLKAVKEYI = ART.ARTKAVKEYI) as prodinstructions
                  from group by clause.

                  ----