locked
sql error-order by clause not working RRS feed

  • 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 ITEMVALUE

     

    FROM tOPENLINE_MODIFIED

    LEFT OUTER JOIN

    tZCHANNEL ON tOPENLINE_MODIFIED.ZCHANNEL = tZCHANNEL.ZCHANNEL

    WHERE 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 A

    GROUP BY week

     

    output:

    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 t

    order 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) end

     

     

    AMB

    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 t

    order 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) end

     

     

    AMB

    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