Group by SUBSTR

Question

Can someone explain to me why the following code works with no errors:

SELECT t1.column1,
       t1.column2,
       t1.column3,
       ctot as Cost,
       (ctot * t1.qty) as FinalCost
FROM Table1 t1

LEFT JOIN (SELECT column1,
                  column2,
                  column3,
                  column4,
             MAX(DateCol),
             AVG(CostCol) KEEP(DENSE_RANK LAST ORDER BY t2.DateCol) as ctot
FROM Table2 t2
GROUP BY column1, column2, SUBSTR(column3, 1, 5), column4) t2
     ON t2.column1 = t1.column1
     AND t2.column2 = t1.column2
     AND t2.column4 = t1.column4
     AND SUBSTR(t1.column3, 1, 5) = SUBSTR(t2.column3, 1, 5)
     WHERE t1.ID1 = '500'
     AND t1.ID2 != '50'

While this code will produce error ORA-00979: not a GROUP BY expression:

SELECT t1.column1,
       t1.column2,
       t1.column3,
       ctot as Cost,
       (ctot * t1.qty) as FinalCost
FROM Table1 t1

LEFT JOIN Table2 t2
    ON t1.column1 = t2.column1
   AND t1.column2 = t2.column2

LEFT JOIN (SELECT IdCol as points, column3 FROM Table3) t3
    ON t2.column3 = t3.column3

LEFT JOIN (SELECT column1,
                  column3,
             MAX(DateCol),
             AVG(CostCol) KEEP(DENSE_RANK LAST ORDER BY t4.DateCol) as ctot
FROM Table4 t4
GROUP BY column1, SUBSTR(column3, 1, 5)) t4
    ON t4.column1 = points
   AND SUBSTR(t4.column3, 1, 5) = SUBSTR(t1.column3, 1, 5)
 WHERE t1.ID1 = '500'
   AND t1.ID2 != '50'

My editor highlights column3 in my final join statement as the cause of the error, and replacing SUBSTR(column3, 1, 5) with column3 in the GROUP BY statement fixes this error. But why is the error being thrown in the second example but not the first? Does it have to do with the multiple join statements?

Answer 1

You are using LEFT JOIN in main select, thats mean Oracle is using SQL92. So you must use in GROUP BY clausule exat part of SELECT projection.

e.i. use this in subselect:

SELECT column1,
    column2,
    SUBSTR(column3, 1, 5) as column3, -- column3 after substr()
    column4,
    MAX(DateCol),
    AVG(CostCol) KEEP(DENSE_RANK LAST ORDER BY t2.DateCol) AS ctot
FROM Table2 t2
GROUP BY column1, column2, SUBSTR(column3, 1, 5), column4