Oracle 10G migration Issues - not group by

When we migrated from Oracle 9i to 10G, we encountered a few issues. One of the strangest one is as follows.

The following query works fine in 9i but when we execute in 10G, we encounter the following error:

ORA-00979: not a GROUP BY expression

The SQL statement (changes have been made on the column names and table names to protect the innocent code) is as follows:

 SELECT TO_CHAR(Time_Date, 'yyyymmdd') Time_Date                                   ,
  Def_Code                                                                         ,
  StepName                                                                         ,
  ScrapReason                                                                       ,
  StartQty                                                                         ,
  ScrapQty                                                                        ,
  BonusQty                                                                         ,
  ROUND(DECODE(StartQty,0,0,(ScrapQty-BonusQty)/StartQty*100), 2) DefectPercentage,
  0 Normalised
   FROM
  (SELECT Time_Date,
    Def_Code       ,
    StepName       ,
    ScrapReason     ,
    ScrapQty      ,
    BonusQty       ,
    (SELECT SUM(YLDPACKAGE.GET_OPSTEPSTARTQTY(StepName,StartQty,ScrapQty,BonusQty))
       FROM yld_summary
      WHERE Time_Date = d.Time_Date
    AND RType         = 'ASSEMBLY'
    AND (Owner LIKE 'SPECIALBUILD%'
    AND (Category                      = 'PRODUCTION'
    OR Category                        = 'BETA' ) )
    AND (PRODUCTNAME(WafID,Owner)       = 'VISTA')
    AND YLDPACKAGE.GET_STEPGRP(StepName) = d.StepName
    ) StartQty
     FROM
    (SELECT Time_Date                         ,
      Def_Code                                ,
      YLDPACKAGE.GET_STEPGRP(StepName) StepName ,
      REPLACE(ScrapReason, '_RI','') ScrapReason,
      SUM(ScrapQty) ScrapQty                ,
      SUM(BonusQty) BonusQty
       FROM yld_dtl
      WHERE Time_Date BETWEEN to_date('20081007', 'yyyymmdd') AND to_date('20081014', 'yyyymmdd')
    AND RType = 'ASSEMBLY'
    AND (Owner LIKE 'SPECIALBUILD%'
    AND (Category                      = 'PRODUCTION'
    OR Category                        = 'BETA' ) )
    AND (PRODUCTNAME(WafID,Owner)       = 'VISTA')
    AND YLDPACKAGE.GET_STEPGRP(StepName) = 'LAPPING'
    AND ScrapReason                     = 'Scratches'
   GROUP BY Time_Date               ,
      Def_Code                      ,
      YLDPACKAGE.GET_STEPGRP(StepName),
      REPLACE(ScrapReason, '_RI','')
    ) d
  ) order by time_date

The work around is to just add another column in the group by (highlighted below in red):

 SELECT TO_CHAR(Time_Date, 'yyyymmdd') Time_Date                                   ,
  Def_Code                                                                         ,
  StepName                                                                         ,
  ScrapReason                                                                       ,
  StartQty                                                                         ,
  ScrapQty                                                                        ,
  BonusQty                                                                         ,
  ROUND(DECODE(StartQty,0,0,(ScrapQty-BonusQty)/StartQty*100), 2) DefectPercentage,
  0 Normalised
   FROM
  (SELECT Time_Date,
    Def_Code       ,
    StepName       ,
    ScrapReason     ,
    ScrapQty      ,
    BonusQty       ,
    (SELECT SUM(YLDPACKAGE.GET_OPSTEPSTARTQTY(StepName,StartQty,ScrapQty,BonusQty))
       FROM yld_summary
      WHERE Time_Date = d.Time_Date
    AND RType         = 'ASSEMBLY'
    AND (Owner LIKE 'SPECIALBUILD%'
    AND (Category                      = 'PRODUCTION'
    OR Category                        = 'BETA' ) )
    AND (PRODUCTNAME(WafID,Owner)       = 'VISTA')
    AND YLDPACKAGE.GET_STEPGRP(StepName) = d.StepName
    ) StartQty
     FROM
    (SELECT Time_Date                         ,
      Def_Code                                ,
      YLDPACKAGE.GET_STEPGRP(StepName) StepName ,
      REPLACE(ScrapReason, '_RI','') ScrapReason,
      SUM(ScrapQty) ScrapQty                ,
      SUM(BonusQty) BonusQty
       FROM yld_dtl
      WHERE Time_Date BETWEEN to_date('20081007', 'yyyymmdd') AND to_date('20081014', 'yyyymmdd')
    AND RType = 'ASSEMBLY'
    AND (Owner LIKE 'SPECIALBUILD%'
    AND (Category                      = 'PRODUCTION'
    OR Category                        = 'BETA' ) )
    AND (PRODUCTNAME(WafID,Owner)       = 'VISTA')
    AND YLDPACKAGE.GET_STEPGRP(StepName) = 'LAPPING'
    AND ScrapReason                     = 'Scratches'
   GROUP BY Time_Date               ,
      Def_Code                      ,
      YLDPACKAGE.GET_STEPGRP(StepName),
      StepName                      ,
      REPLACE(ScrapReason, '_RI','')
    ) d
  ) order by time_date

Nov 23, 2008

Found the above code did not product correct output, had to rewrite it as follows:

 SELECT TO_CHAR(Time_Date, 'yyyymmdd') Time_Date                                   ,
  Def_Code                                                                         ,
  StepName                                                                         ,
  ScrapReason                                                                       ,
  StartQty                                                                         ,
  ScrapQty                                                                        ,
  BonusQty                                                                         ,
  ROUND(DECODE(StartQty,0,0,(ScrapQty-BonusQty)/StartQty*100), 2) DefectPercentage,
  0 Normalised
   FROM
  (SELECT Time_Date,
    Def_Code       ,
    StepName       ,
    ScrapReason     ,
    ScrapQty      ,
    BonusQty       ,
    (SELECT SUM(YLDPACKAGE.GET_OPSTEPSTARTQTY(StepName,StartQty,ScrapQty,BonusQty)), 
       YLDPACKAGE.GET_STEPGRP(StepName) step
      FROM yld_summary
      WHERE Time_Date = d.Time_Date
    AND RType         = 'ASSEMBLY'
    AND (Owner LIKE 'SPECIALBUILD%'
    AND (Category                      = 'PRODUCTION'
    OR Category                        = 'BETA' ) )
    AND (PRODUCTNAME(WafID,Owner)       = 'VISTA')
    group by YLDPACKAGE.GET_STEPGRP(StepName)) l,
    (SELECT Time_Date                         ,
      Def_Code                                ,
      YLDPACKAGE.GET_STEPGRP(StepName) StepName ,
      REPLACE(ScrapReason, '_RI','') ScrapReason,
      SUM(ScrapQty) ScrapQty                ,
      SUM(BonusQty) BonusQty
       FROM yld_dtl
      WHERE Time_Date BETWEEN to_date('20081007', 'yyyymmdd') AND to_date('20081014', 'yyyymmdd')
    AND RType = 'ASSEMBLY'
    AND (Owner LIKE 'SPECIALBUILD%'
    AND (Category                      = 'PRODUCTION'
    OR Category                        = 'BETA' ) )
    AND (PRODUCTNAME(WafID,Owner)       = 'VISTA')
    AND YLDPACKAGE.GET_STEPGRP(StepName) = 'LAPPING'
    AND ScrapReason                     = 'Scratches'
   GROUP BY Time_Date               ,
      Def_Code                      ,
      YLDPACKAGE.GET_STEPGRP(StepName),
      StepName                      ,
      REPLACE(ScrapReason, '_RI','')
    ) d where l.step = d.stepname
  ) order by time_date