Answered by:
sql error-order by clause not working

Question
-
Hi,
I have the following code and output
select
distinct week ,sum(itemvalue) as itemvalue from (Select ATP,WEEK
=CASE
WHEN
(datepart(Dd, ATP) < 7 AND datename(Month,ATP)='JANUARY')OR
(
datepart(Dd, ATP) < 8 AND datename(Month,ATP)='JULY')OR
(
datepart(Dd, ATP) < 7 AND datename(Month,ATP)='OCTOBER') THEN '1'WHEN
(datepart(Dd, ATP) < 14 AND datename(Month,ATP)='JANUARY')OR
(
datepart(Dd, ATP) < 15 AND datename(Month,ATP)='JULY')OR
(
datepart(Dd, ATP) < 14 AND datename(Month,ATP)='OCTOBER') THEN '2'WHEN
(datepart(Dd, ATP) < 21 AND datename(Month,ATP)='JANUARY')OR
(
datepart(Dd, ATP) < 22 AND datename(Month,ATP)='JULY')OR
(
datepart(Dd, ATP) < 21 AND datename(Month,ATP)='OCTOBER') THEN '3'WHEN
(datepart(Dd, ATP) < 28 AND datename(Month,ATP)='JANUARY')OR
(
datepart(Dd, ATP) < 29 AND datename(Month,ATP)='JULY')OR
(
datepart(Dd, ATP) < 28 AND datename(Month,ATP)='OCTOBER') THEN '4'WHEN
((datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JANUARY')OR
(
(datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JULY')OR
(
(datepart(Dd, ATP) IN (28,29,30,31)) AND datename(Month,ATP)='OCTOBER') THEN '5'WHEN
(datepart(Dd, ATP) < 4 AND datename(Month,ATP)='FEBRUARY')(
datepart(Dd, ATP) < 5 AND datename(Month,ATP)='AUGUST')OR
(
datepart(Dd, ATP) < 4 AND datename(Month,ATP)='NOVEMBER') THEN '5'WHEN
(datepart(Dd, ATP) <11 AND datename(Month,ATP)='FEBRUARY')OR
(
datepart(Dd, ATP) < 13 AND datename(Month,ATP)='MAY')OR
(
datepart(Dd, ATP) < 12 AND datename(Month,ATP)='AUGUST')OR
(
datepart(Dd, ATP) < 11 AND datename(Month,ATP)='NOVEMBER') THEN '6'WHEN
(datepart(Dd, ATP) < 18 AND datename(Month,ATP)='FEBRUARY')OR
(
datepart(Dd, ATP) < 19 AND datename(Month,ATP)='AUGUST')OR
(
datepart(Dd, ATP) < 18 AND datename(Month,ATP)='NOVEMBER') THEN '7'WHEN
(datepart(Dd, ATP) < 25 AND datename(Month,ATP)='FEBRUARY')OR
(
datepart(Dd, ATP) < 27 AND datename(Month,ATP)='MAY')OR
(
datepart(Dd, ATP) < 31 AND datename(Month,ATP)='AUGUST')OR
(
datepart(Dd, ATP) < 25 AND datename(Month,ATP)='NOVEMBER') THEN '8'WHEN
((datepart(Dd, ATP) IN (25,26,27,28)) AND datename(Month,ATP)='FEBRUARY')OR
(
(datepart(Dd, ATP) IN (27,28,29,30,31)) AND datename(Month,ATP)='MAY')OR
( (datepart(Dd, ATP) IN (25,26,27,28,29,30)) AND datename(Month,ATP)='NOVEMBER')
OR
(
(datepart(Dd, ATP) <2) AND datename(Month,ATP)='DECEMBER') THEN '9'WHEN
(datepart(Dd, ATP) < 11 AND datename(Month,ATP)='MARCH')OR
(
datepart(Dd, ATP) < 9 AND datename(Month,ATP)='SEPTEMBER')OR
(
datepart(Dd, ATP) < 9 AND datename(Month,ATP)='DECEMBER') THEN '10'WHEN
(datepart(Dd, ATP) < 18 AND datename(Month,ATP)='MARCH')OR
(
datepart(Dd, ATP) < 16 AND datename(Month,ATP)='SEPTEMBER')OR
(
datepart(Dd, ATP) < 16 AND datename(Month,ATP)='DECEMBER') THEN '11'WHEN
(datepart(Dd, ATP) < 25 AND datename(Month,ATP)='MARCH')OR
(
datepart(Dd, ATP) < 23 AND datename(Month,ATP)='SEPTEMBER')OR
(
datepart(Dd, ATP) < 23 AND datename(Month,ATP)='DECEMBER') THEN '12'WHEN
(datepart(Dd, ATP) > 24 AND datename(Month,ATP)='MARCH')OR
(
datepart(Dd, ATP) > 22 AND datename(Month,ATP)='SEPTEMBER')OR
(
datepart(Dd, ATP) < 30 AND datename(Month,ATP)='DECEMBER') THEN '13'ELSE
'BEYOND'END
, SUM(ITEMVALUE) as ITEMVALUEFROM
tOPENLINE_MODIFIEDLEFT
OUTER JOINtZCHANNEL
ON tOPENLINE_MODIFIED.ZCHANNEL = tZCHANNEL.ZCHANNELWHERE
RequestQtr in ('Q4')and
tOPENLINE_MODIFIED.ATP >= '7/01/07'and
tOPENLINE_MODIFIED.ORDERTYPE in ('OR','ZBOS','ZECM','ZOR','ZOB','ZEXP')and
dbo.tZCHANNEL.ZCHANNEL in ('D','I','01', '02', '06', '07', '10')and
tOPENLINE_MODIFIED.ACCTASSIGNGRP in ('01','02')AND
tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%celestica%')AND
tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%giant%')and
tOPENLINE_MODIFIED.PLANT IN ('COF1', 'I405', 'I375', 'IOM4', 'IOM5', 'I316')GROUP
BY ATP)as AGROUP
BY weekoutput:
week itemvalue
------ ---------------------
1 1214003.60
10 9257193.45
11 12095432.11
12 11429629.08
13 7315751.08
2 1052337.53
3 951038.10
4 274769.21
5 465278.37
6 78003.67
7 607681.02
8 9042948.17
9 2255545.25
but i need the output as
week
1
2
3
4
5
6
7
8
9
10
11
12
13
iam not able to achieve this after trying so many times.Please help me on this.
Thanks,
SVGP
Monday, August 27, 2007 7:22 PM
Answers
-
Try:
select
*from
(here your query witout "order by" clause
) as torder
by case when [week] not like '%[^0-9]%' then 1 else 2 end, case when [week] not like '%[^0-9]%' then cast([week] as int) else cast(null as int) endAMB
Monday, August 27, 2007 7:44 PM
All replies
-
after group by week i have included
order by week -statement as well,still its not working.
Monday, August 27, 2007 7:26 PM -
Try:
select
*from
(here your query witout "order by" clause
) as torder
by case when [week] not like '%[^0-9]%' then 1 else 2 end, case when [week] not like '%[^0-9]%' then cast([week] as int) else cast(null as int) endAMB
Monday, August 27, 2007 7:44 PM -
Hi,
Thank you so much,its working,but i didnt undersatnd what we are doing in order by clause
will you pls explain when you get time.
Thank you once again.
SVGP
Monday, August 27, 2007 7:57 PM -
The possible values for [week] are ('1', '2', ..., '13', 'BEYOND').
I am using two members in the list of the "order by" clause. The first one order by the value of the first "case" expression, which returns 1 for numeric values and 2 for not numeric. The second one is the cast to INT for numeric values and NULL for no numeric.
AMB
Monday, August 27, 2007 8:09 PM -
got it.wonderful.
thanks for making me understand.
Regards,
SVGP.
Monday, August 27, 2007 8:12 PM