Order by clause don't work in SQL server 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by clause don't work in SQL server 2008

Author

Topic

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2011-04-19 : 09:58:44

Hi
I am migratting my sp from sql server 2000 to sql server 2008. But my sp do not execute in sql server 2008.
Please find the sp below:
SELECT
CASE
WHEN SUBSTRING([CUBE], 1, 1) = 'E' THEN SUBSTRING([CUBE], 1, 3)
ELSE SUBSTRING([CUBE], 1, 2)
END AS [Row],
CASE
WHEN SUBSTRING([CUBE], 1, 1) = 'E' THEN SUBSTRING([CUBE], 4, 2)
ELSE SUBSTRING([CUBE], 3, 2)
END AS [Section],
CASE
WHEN SUBSTRING([CUBE], 1, 1) = 'E' THEN SUBSTRING([CUBE], 6, 1)
ELSE SUBSTRING([CUBE], 5, 1)
END AS [Level],
CASE
WHEN SUBSTRING([CUBE], 1, 1) = 'E' THEN NULL
ELSE SUBSTRING([CUBE], 6, 1)
END AS [Position]
FROM
(
SELECT
L.WH,
L.CUBE,
P.OWNERID,
P.OWNERNAME,
P.OWNERDEPT,
CASE
WHEN P.LOCATION IS NOT NULL AND P.SPOT IS NOT NULL THEN 1
ELSE 0
END AS TAKEN,
L.DEPT,
L.USED
FROM LOCATION L
LEFT OUTER JOIN PALLET P ON (L.WH = P.LOCATION AND L.CUBE = P.SPOT)
) as WRHS
where WRHS.WH = 'kalyan' AND WRHS.TAKEN = 0
ORDER BY WRHS.[Row], WRHS.[Section], WRHS.[Level], WRHS.[Position]

While executing this give me error message that
Msg 207, Level 16, State 1, Line 45
Invalid column name 'Row'.
Msg 207, Level 16, State 1, Line 45
Invalid column name 'Section'.
Msg 207, Level 16, State 1, Line 45
Invalid column name 'Level'.
Msg 207, Level 16, State 1, Line 45
Invalid column name 'Position'.
Msg 207, Level 16, State 1, Line 87

Could some one please help me.

kalyan Ashis Dey

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-19 : 10:03:05

SELECT CASE WHEN SUBSTRING([CUBE], 1, 1) = 'E' THEN SUBSTRING([CUBE], 1, 3)
ELSE SUBSTRING([CUBE], 1, 2)
END AS [Row],
CASE WHEN SUBSTRING([CUBE], 1, 1) = 'E' THEN SUBSTRING([CUBE], 4, 2)
ELSE SUBSTRING([CUBE], 3, 2)
END AS [Section],
CASE WHEN SUBSTRING([CUBE], 1, 1) = 'E' THEN SUBSTRING([CUBE], 6, 1)
ELSE SUBSTRING([CUBE], 5, 1)
END AS [Level],
CASE WHEN SUBSTRING([CUBE], 1, 1) = 'E' THEN NULL
ELSE SUBSTRING([CUBE], 6, 1)
END AS [Position]
FROM ( SELECT L.WH,
L.CUBE,
P.OWNERID,
P.OWNERNAME,
P.OWNERDEPT,
CASE WHEN P.LOCATION IS NOT NULL
AND P.SPOT IS NOT NULL THEN 1
ELSE 0
END AS TAKEN,
L.DEPT,
L.USED
FROM LOCATION L
LEFT OUTER JOIN PALLET P ON ( L.WH = P.LOCATION
AND L.CUBE = P.SPOT
)
) as WRHS
where WRHS.WH = 'kalyan'
AND WRHS.TAKEN = 0
ORDER BY ~~WRHS.~~[Row],
~~WRHS.~~[Section],
~~WRHS.~~[Level],
~~WRHS.~~[Position]

--------------------------
http://connectsql.blogspot.com/

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2011-04-19 : 10:10:43

Thanks a lot it got fixed :):)

kalyan Ashis Dey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources