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.
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 intDeclare @ll_Sort intDeclare @ll_ExportedStatusID intset @ll_EventStatusID =1set @ll_Sort =4set @ll_ExportedStatusID= 0SELECTE.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 EINNER JOIN POS2000.dbo.Location as LON E.LocationID = L.IDINNER JOIN POS2000.dbo.Location as Aon E.AreaID = A.IDINNER JOIN EventType as ETON E.EventTypeID = ET.IDINNER JOIN Status as SON E.StatusID = S.IDFULL JOIN (SELECT EntityID, EventID, IsPrimaryContact FROM EventEntity WHERE IsPrimaryContact = 1 GROUP BY Entityid, IsPrimaryContact, EventID) as EEON E.ID = EE.EventIDLEFT JOIN ctcPeople.dbo.Entity as ENTITYON EE.EntityID = ENTITY.ID INNER JOIN ctcAdmin.dbo.Employee as EMPLOYEEON E.BookedEmployeeID = EMPLOYEE.IDWHERE (e.EventStatusENUM = @ll_EventStatusIDOR 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 ENDGO |
|
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) |
|
|
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? |
|
|
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? |
|
|
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. |
|
|
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/04CAST doesn't support the style option for DATETIME like CONVERT. |
|
|
JAdauto
Posting Yak Master
160 Posts |
Posted - 2005-08-31 : 14:35:19
|
Works like a charm now. Muchos muchos gracias! |
|
|
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) ENDHowever, 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 ENDNow 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 |
|
|
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 |
|
|
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 #tselect ... 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 ...ENDexec ( @sql ) CODO ERGO SUM |
|
|
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 |
|
|
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 |
|
|
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 = 1Set @ll_ExportedStatusID = 0Set @ll_Sort = 4Create 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 #tempQuickLookupSELECTE.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 EINNER JOIN POS2000.dbo.Location as LON E.LocationID = L.IDINNER JOIN POS2000.dbo.Location as Aon E.AreaID = A.IDINNER JOIN EventType as ETON E.EventTypeID = ET.IDINNER JOIN Status as SON E.StatusID = S.IDFULL JOIN (SELECT EntityID, EventID, IsPrimaryContact FROM EventEntity WHERE IsPrimaryContact = 1 GROUP BY Entityid, IsPrimaryContact, EventID) as EEON E.ID = EE.EventIDLEFT JOIN ctcPeople.dbo.Entity as ENTITYON EE.EntityID = ENTITY.ID FULL JOIN ctcAdmin.dbo.Employee as EMPLOYEEON E.BookedEmployeeID = EMPLOYEE.IDWHERE (e.EventStatusENUM = @ll_EventStatusIDOR 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' ENDEXEC (@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. |
|
|
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 |
|
|
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 thisORDER BY Case @ll_Sort WHEN 0 THEN convert(varchar,E.StartDate ,120)WHEN 1 THEN convert(varchar,E.StartDate ,120)WHEN 2 THEN E.EventTypeIDWHEN 3 THEN E.StatusID WHEN 4 THEN E.ContractNumberENDGOshubhada |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-13 : 06:51:01
|
shubhada, JAdauto's problem is actually thisquote: 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 |
|
|
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. |
|
|
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 31String or binary data would be truncated.The statement has been terminated.Could I have set some datatypes incorrectly? |
|
|
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.SELECTE.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 0WHEN 1 THEN 0WHEN 2 THEN 0WHEN 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 Sort2FROM Event as EINNER JOIN POS2000.dbo.Location as LON E.LocationID = L.IDINNER JOIN POS2000.dbo.Location as Aon E.AreaID = A.IDINNER JOIN EventType as ETON E.EventTypeID = ET.IDINNER JOIN Status as SON E.StatusID = S.IDFULL JOIN (SELECT EntityID, EventID, IsPrimaryContact FROM EventEntity WHERE IsPrimaryContact = 1 GROUP BY Entityid, IsPrimaryContact, EventID) as EEON E.ID = EE.EventIDLEFT JOIN ctcPeople.dbo.Entity as ENTITYON EE.EntityID = ENTITY.ID FULL JOIN ctcAdmin.dbo.Employee as EMPLOYEEON E.BookedEmployeeID = EMPLOYEE.IDWHERE (e.EventStatusENUM = @ll_EventStatusIDOR e.EventStatusENUM = @ll_ExportedStatusID)AND (e.IsHoliday = 0)ORDER BY Sort1,Sort2, E.ContractNumberGO |
|
|
|
|
|
|
|