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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Error with Order By Case

Author  Topic 

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-08-31 : 11:52:53
I have the query below in a stored procedure: It is long, but only one small portion does not work. When I try to Sort by ContractNumber (which is a varchar datatype), it gives me a syntax error: Syntax error converting datetime from character string. All other sort options work and return data. But when I select to sort by ContractNumber, it does not. Any ideas would be so much appreciated.



Declare @ll_EventStatusID int
Declare @ll_Sort int
Declare @ll_ExportedStatusID int

set @ll_EventStatusID =1
set @ll_Sort =4
set @ll_ExportedStatusID= 0

SELECT
E.ID as 'EventID',
E.ContractNumber as 'ContractNumber',
L.Description as 'EventLocation',
A.Description as 'EventArea',
E.EventName as 'EventName',
ET.Description as 'EventType',
S.Description as 'EventStatus',
E.TentativeCount as 'TentativeCount',
E.GuaranteedCount as 'GuaranteedCount',
E.ConfirmedCount as 'ConfirmedCount',
E.StartDate as 'StartDate',
E.EndDate as 'EndDate',
(ENTITY.FirstName + ' ' + ENTITY.LastName) as 'PrimaryContact',
(EMPLOYEE.firstName + ' ' + EMPLOYEE.LastName) as 'BookedByEmployee',
E.BookedDate as 'BookedDate'

FROM Event as E
INNER JOIN POS2000.dbo.Location as L
ON E.LocationID = L.ID
INNER JOIN POS2000.dbo.Location as A
on E.AreaID = A.ID
INNER JOIN EventType as ET
ON E.EventTypeID = ET.ID
INNER JOIN Status as S
ON E.StatusID = S.ID
FULL JOIN
(SELECT EntityID, EventID, IsPrimaryContact
FROM EventEntity
WHERE IsPrimaryContact = 1
GROUP BY Entityid, IsPrimaryContact, EventID) as EE
ON E.ID = EE.EventID
LEFT JOIN ctcPeople.dbo.Entity as ENTITY
ON EE.EntityID = ENTITY.ID
INNER JOIN ctcAdmin.dbo.Employee as EMPLOYEE
ON E.BookedEmployeeID = EMPLOYEE.ID

WHERE (e.EventStatusENUM = @ll_EventStatusID
OR e.EventStatusENUM = @ll_ExportedStatusID)
AND (e.IsHoliday = 0)

ORDER BY Case @ll_Sort
WHEN 0 THEN E.StartDate
WHEN 1 THEN E.StartDate
WHEN 2 THEN E.EventTypeID
WHEN 3 THEN E.StatusID
WHEN 4 THEN E.ContractNumber
END
GO

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-31 : 13:14:34
All the ORDER BY CASE elements must be the same DATATYPE (unfortunately).

Try making them all VARCHAR... e.g. CAST (E.StartDate as VARCHAR)
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-08-31 : 13:32:42
I have read all the help files and BOL, but I dont get if there is a benefit to Cast over Convert or vice versa. Right before I checked your reply, I had done similar but with Convert. Is there a prefered method?
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-08-31 : 13:33:10
I have read all the help files and BOL, but I dont get if there is a benefit to Cast over Convert or vice versa. Right before I checked your reply, I had done similar but with Convert. Is there a prefered method?
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-08-31 : 14:02:00
Well whether I do Cast or Convert to varchar, I still get the same results. I am now getting data when the sort option = 4 for Contract Number. But when it is sorted now by StartDate, it displays the data, but it is not sorted correctly. I dont know how to get all these different types of data into one data type to get a valid sort no matter what they choose.

Thanks for the help thus far.
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-31 : 14:28:33
Use CONVERT for the DATETIME columns, but "style" the VARCHAR result so it'll be sorted properly...

eg... dates like Jan 4, 2003 -- Will not sort properly, but the format 20030104 -- should sort properly as will 2003/01/04

CAST doesn't support the style option for DATETIME like CONVERT.
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2005-08-31 : 14:35:19
Works like a charm now. Muchos muchos gracias!
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-02-10 : 22:30:47
It has been a while since I have had to address this issue, but once again, it is back to me. My sort portion of the query looks like this now:

ORDER BY Case @ll_Sort
WHEN 0 THEN Convert(varchar,E.StartDate,101)
WHEN 1 THEN Convert(varchar,E.StartDate,101)
WHEN 2 THEN Convert(varchar,E.EventTypeID)
WHEN 3 THEN Convert(varchar,E.StatusID)
WHEN 4 THEN Convert(varchar,E.ContractNumber)
END

However, now my problem is that our clients are complaining because E.ContractNumber is a varchar field, but can contain only numeric data if they choose to. So, when they sort by Contract Number, it will sort like 1,10,11,2,3,4,5,6,7,8,9.

I remembered that all the values had to be the same data type, so I dont know if this is possible. What I attempted to do was:

ORDER BY Case @ll_Sort
WHEN 0 THEN Convert(varchar,E.StartDate,101)
WHEN 1 THEN Convert(varchar,E.StartDate,101)
WHEN 2 THEN Convert(varchar,E.EventTypeID)
WHEN 3 THEN Convert(varchar,E.StatusID)
WHEN 4 THEN
CASE L.NumericCatContractNumbers
WHEN 1 THEN Convert(int,E.ContractNumber)
ELSE Convert(varchar,E.ContractNumber)
END
END

Now obviously I know that will not work because I am not converting them all the same. But I did not know if someone knows a common data type that will work for everything under the sun. :)

Thanks,
JAdauto
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-10 : 23:03:05
You might also want to consider dynamic SQL ? exec() or sp_executesql

----------------------------------
'KH'

everything that has a beginning has an end
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-10 : 23:11:14
It's more flexible to create a temp table, insert your query result set into the temp table, and then execute a dynamic SQL statement to order the output the way you want.

It is a lot easier to do it this way when people want a lot of flexibility to pick the columns to sort by, sort by multiple columns, or sort ascending or descending.

create table #t ( ... column list... )

insert into #t
select ... your query ...


declare @sql varchar(8000)

select @sql =
'select * from #t order by '+
Case @ll_Sort
WHEN 0 THEN 'StartDate'
WHEN 1 THEN 'StartDate desc'
WHEN 2 THEN 'EventTypeID'
WHEN 3 THEN 'StatusID'
WHEN 5 THEN 'EventName'
WHEN 6 THEN 'StartDate, EndDate'
WHEN 7 THEN 'BookedDate'
WHEN 8 THEN 'BookedDate desc'
... any other variations you can think of ...
END

exec ( @sql )






CODO ERGO SUM
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-02-13 : 00:07:02
In an effort to not sound like I am trying to be difficult, I want to say that I really appreciate you guys and your input. I dont think a temporary table is going to work for my situation because we access all of our data via a middle tier of data objects that have specific user permissions. So, unless I am missing something, I dont think it is gonna work in our architecture. We tried a temporay table before and ended up having to create an actual table that we use to export the temporary data into so that we could get to the data via our data objects and user. That will not work for this because they can select a certain number of fields that can be used or not used. In addition, in this one portion of our app, we are avoiding using a table to hold the data at all so that as we add fields that can be choosen from, we dont have to make database design changes. We just update our stored procedures for available fields and then update the stored procedure for the values for each field, drop them in and then they are updated. If I had to create a table as we did before (our solution to a temp table) we would loose that functionality. I appreciate the suggestion, but in our case, it is just not gonna work I dont think. I welcome any other ideas because I am about a day away from having to tell the client they are SOL if they want to sort by contract numbers numerically, and that is a really crappy feeling when you think you can do anything. :)

Thanks so much,
JAdauto
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-13 : 00:29:04
I didn't see anything in that post that would explain why you could not use a temp table and dynamic SQL.


CODO ERGO SUM
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-02-13 : 01:46:38
Maybe I have misunderstood the purpose and abilities of the temp table. I am trying it first in QA and if I can get it work there, I'll figure out how to get it called in our app. Here is what I have:

Declare @ll_EventStatusID varchar(4)
Declare @ll_ExportedStatusID varchar (4)
Declare @ll_Sort varchar (4)
declare @sql varchar(8000)

set @ll_EventStatusID = 1
Set @ll_ExportedStatusID = 0
Set @ll_Sort = 4

Create Table #tempQuickLookup
(EventID varchar,
ContractNumber varchar,
EventLocation varchar,
EventArea varchar,
EventName varchar,
EventType varchar,
EventStatus varchar,
EstimatedAttendance int,
GuaranteedAttendance int,
ActualAttendance int,
StartDate smalldatetime,
EndDate smalldatetime,
PrimaryContact varchar,
BookedByEmployee varchar,
Bookdate smalldatetime,
Rooms varchar,
NumericContractNumber int)

INSERT INTO #tempQuickLookup

SELECT
E.ID as 'EventID',
E.ContractNumber as 'ContractNumber',
L.Description as 'EventLocation',
A.Description as 'EventArea',
E.EventName as 'EventName',
ET.Description as 'EventType',
S.Description as 'EventStatus',
E.TentativeCount as 'EstimatedAttendance',
E.GuaranteedCount as 'GuaranteedAttendance',
E.ConfirmedCount as 'ActualAttendance',
E.StartDate as 'StartDate',
E.EndDate as 'EndDate',
(ENTITY.FirstName + ' ' + ENTITY.LastName) as 'PrimaryContact',
(EMPLOYEE.firstName + ' ' + EMPLOYEE.LastName) as 'BookedByEmployee',
E.BookedDate as 'BookedDate',
'' as 'Rooms',
Convert(int,E.contractNumber) as 'NumericContractNumber'

FROM Event as E
INNER JOIN POS2000.dbo.Location as L
ON E.LocationID = L.ID
INNER JOIN POS2000.dbo.Location as A
on E.AreaID = A.ID
INNER JOIN EventType as ET
ON E.EventTypeID = ET.ID
INNER JOIN Status as S
ON E.StatusID = S.ID
FULL JOIN
(SELECT EntityID, EventID, IsPrimaryContact
FROM EventEntity
WHERE IsPrimaryContact = 1
GROUP BY Entityid, IsPrimaryContact, EventID) as EE
ON E.ID = EE.EventID
LEFT JOIN ctcPeople.dbo.Entity as ENTITY
ON EE.EntityID = ENTITY.ID
FULL JOIN ctcAdmin.dbo.Employee as EMPLOYEE
ON E.BookedEmployeeID = EMPLOYEE.ID

WHERE (e.EventStatusENUM = @ll_EventStatusID
OR e.EventStatusENUM = @ll_ExportedStatusID)
AND (e.IsHoliday = 0)


select @sql = 'select * from #tempQuickLookup' ORDER BY +
Case @ll_Sort
WHEN 0 THEN 'StartDate'
WHEN 1 THEN 'StartDate'
WHEN 2 THEN 'EventType'
WHEN 3 THEN 'EventStatus'
WHEN 4 THEN 'NumericContractNumber'
WHEN 5 THEN 'ContractNumber'
END

EXEC (@sql)


HERE is the error I am getting in QA:

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

I looked up the error in BOL, but that was no help. Any suggestions on what I have done incorrectly? Thx so much.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-13 : 06:30:20
Change to
select @sql = 'SELECT * FROM #tempQuickLookup ORDER BY ' +


----------------------------------
'KH'

everything that has a beginning has an end
Go to Top of Page

shubhada
Posting Yak Master

117 Posts

Posted - 2006-02-13 : 06:41:58
I was facing such a problem...
that time i used convert function for date.
Try this
ORDER BY Case @ll_Sort
WHEN 0 THEN convert(varchar,E.StartDate ,120)
WHEN 1 THEN convert(varchar,E.StartDate ,120)
WHEN 2 THEN E.EventTypeID
WHEN 3 THEN E.StatusID
WHEN 4 THEN E.ContractNumber
END
GO


shubhada
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-13 : 06:51:01
shubhada, JAdauto's problem is actually this
quote:
However, now my problem is that our clients are complaining because E.ContractNumber is a varchar field, but can contain only numeric data if they choose to. So, when they sort by Contract Number, it will sort like 1,10,11,2,3,4,5,6,7,8,9.


----------------------------------
'KH'

everything that has a beginning has an end
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-13 : 07:35:25
There is another technique you can apply:
order by 
case when @llsort between 0 and 1 then e.startDate end,
case when @llsort = 2 then e.EventTypeID end,
case when @llsort = 3 then e.statusID end,
case when @llsort = 4 then e.contractNumber end
It is probably less efficient solution but easier to code.

You can also package your select into table function and use dynamic sql and/or stored procedure to order table function output.
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-02-13 : 09:32:19
quote:
Originally posted by khtan

Change to
select @sql = 'SELECT * FROM #tempQuickLookup ORDER BY ' +





This got me passed my first error I posted above. Now I am getting this error:

Server: Msg 8152, Level 16, State 9, Line 31
String or binary data would be truncated.
The statement has been terminated.

Could I have set some datatypes incorrectly?
Go to Top of Page

JAdauto
Posting Yak Master

160 Posts

Posted - 2006-02-13 : 11:07:27
As it turns out, my boss who srights all the reports for our apps has dealt with this type of issue before. He provided me with a solution that is working. I thought I would post for anyone's future info. (I know I frequent these forums all the time and learn something new every time). Thanks for all of your input and help. I am hoping to put this issue to rest for good now. Thx again.


SELECT
E.ID as 'EventID',
E.ContractNumber as 'ContractNumber',
L.Description as 'EventLocation',
A.Description as 'EventArea',
E.EventName as 'EventName',
ET.Description as 'EventType',
S.Description as 'EventStatus',
E.TentativeCount as 'EstimatedAttendance',
E.GuaranteedCount as 'GuaranteedAttendance',
E.ConfirmedCount as 'ActualAttendance',
E.StartDate as 'StartDate',
E.EndDate as 'EndDate',
(ENTITY.FirstName + ' ' + ENTITY.LastName) as 'PrimaryContact',
(EMPLOYEE.firstName + ' ' + EMPLOYEE.LastName) as 'BookedByEmployee',
E.BookedDate as 'BookedDate',
'' as 'Rooms',

CASE @ll_Sort
WHEN 0 THEN Convert(varchar,E.StartDate,101)
WHEN 1 THEN Convert(varchar,E.StartDate,101)
WHEN 2 THEN Convert(varchar,E.EventTypeID)
WHEN 3 THEN Convert(varchar,E.StatusID)
WHEN 4 THEN 0 END AS Sort1,

CASE @ll_Sort
WHEN 0 THEN 0
WHEN 1 THEN 0
WHEN 2 THEN 0
WHEN 3 THEN 0
WHEN 4 THEN
CASE WHEN IsNumeric(E.ContractNumber) = 1 THEN Convert(Bigint,E.ContractNumber)
WHEN IsNumeric(E.ContractNumber) = 0 THEN 10000000000000
END
END AS Sort2


FROM Event as E
INNER JOIN POS2000.dbo.Location as L
ON E.LocationID = L.ID
INNER JOIN POS2000.dbo.Location as A
on E.AreaID = A.ID
INNER JOIN EventType as ET
ON E.EventTypeID = ET.ID
INNER JOIN Status as S
ON E.StatusID = S.ID
FULL JOIN
(SELECT EntityID, EventID, IsPrimaryContact
FROM EventEntity
WHERE IsPrimaryContact = 1
GROUP BY Entityid, IsPrimaryContact, EventID) as EE
ON E.ID = EE.EventID
LEFT JOIN ctcPeople.dbo.Entity as ENTITY
ON EE.EntityID = ENTITY.ID
FULL JOIN ctcAdmin.dbo.Employee as EMPLOYEE
ON E.BookedEmployeeID = EMPLOYEE.ID

WHERE (e.EventStatusENUM = @ll_EventStatusID
OR e.EventStatusENUM = @ll_ExportedStatusID)
AND (e.IsHoliday = 0)

ORDER BY Sort1,Sort2, E.ContractNumber
GO
Go to Top of Page
   

- Advertisement -