Re: Not a group by expression

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 15 May 2008 22:20:23 +0200
Message-ID: <482C9B07.5080903@gmail.com>

fitzjarrell_at_cox.net schrieb:
> On May 15, 2:45 pm, "astalavista" <nob..._at_nowhere.com> wrote:
> A fairly easy one to answer -- this
>
> SUBSTR (entry_datetime, :"SYS_B_00", :"SYS_B_01")
>
> in your select list doesn't match this
>
> SUBSTR (entry_datetime, :"SYS_B_11", :"SYS_B_12")
>
> in your GROUP BY.
>
>
> David Fitzjarrell

Not necessarily. This is a tkprof snippet with perfectly valid query :

select substr(ename,:"SYS_B_0",:"SYS_B_1"),deptno,count(*) from emp
group by substr(ename,:"SYS_B_2",:"SYS_B_3"),deptno

call     count       cpu    elapsed       disk      query    current 
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          1          1          0 
        0
Execute      1      0.00       0.00          0          0          0 
        0
Fetch        2      0.00       0.00          1          3          0 
       14

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      0.00       0.00          2          4          0 
       14

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 24

Rows Row Source Operation

-------  ---------------------------------------------------
      14  SORT GROUP BY
      18   TABLE ACCESS FULL EMP

Best regards

Maxim Received on Thu May 15 2008 - 15:20:23 CDT