Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

date_format, group by and order by together

793966Aug 27 2010 — edited Aug 31 2010
Hi,
SELECT
T.foreign_network_id as "Network ID",
date_format(T.ARRIVAL_DATE,'%v-%x') as "Date",
sum(case when((networks_rejected = 0)
and (T.sor_reapply = 'N')
and (T.sor_reason in ('NP', 'NU', 'NT', 'HT')))
then T.CNT else 0 end) as "Before Steering",
sum(case when(sor_reason in ('NP', 'HT', 'T2', 'TM', 'NM', 'MM', 'IPMM', 'NPMM'))
then T.CNT else 0 end) as "After Steering"
FROM ST_INC T,
NETWORKS N,
ZONE_NW_MAP ZN,
ZONES Z,
PAYMENT_TYPE PT
WHERE T.FOREIGN_NETWORK_ID = N.NETWORK_ID
AND ZN.NETWORK_ID = N.NETWORK_ID
AND Z.ZONE_ID = ZN.ZONE_ID
AND PT.id = T.ptype
AND opcode = 2
AND sor_action is not null
and Z.ZONE_ID = 269

group by
date_format(T.ARRIVAL_DATE,'%v-%x'),
T.foreign_network_id
order by date_format(T.ARRIVAL_DATE,'%v-%x')

Above code works but it orders malfunctionally. It orders as if the week-day combination is a string not a date.
I want:
09.2009
10.2009
02.2010
03.2010
06.2011
It outputs:
02.2010
03.2010
06.2011
09.2009
10.2009

I know that the problem arises from the "order by" part. When I call the function date_format I guess a different data type is returnd thus the result is not achieved as intended. So I thought of changing that part only making last line order by T.ARRIVAL_DATE. It worked fine in mysql however it gave error in oracle. This is the error:

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"

When I replace all "date_format(T.ARRIVAL_DATE,'%v-%x')" s with "T.ARRIVAL_DATE" it works fine again. However this is not what i wanted. I want to see the results in the format I gave and group with respect to that fomat, additionally ordering them chronologically. How is that possible?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 28 2010
Added on Aug 27 2010
13 comments
1,579 views