ORDER BY CASE WHEN Syntax error converting character string to smallda 1

[adamroof]

yelp please...

This one fails when 1,2,3,4 is entered (ive done @orderby in int and varchar, same results) Stripped down to basics, still same result...

Syntax error converting character string to smalldatetime data type.

DECLARE @level int, @orderby varchar(1)
SET @level = 3
SET @orderby = '6'
SELECT  Configurations.PartNumber,Configurations.configType,
	Configurations.RevisionNumber,Formulations.MoldOption, Configurations.ODInch,
	Configurations.IDInch, tblAuditActions.auditDate,
	       (SELECT t2.AuditTypeDesc FROM tblAuditActionType t2 WHERE t2.auditTypeID = tblAuditActions.auditTypeID + 1)As AuditTypeDesc, 
		tblAuditActions.auditTypeID
	FROM         Configurations INNER JOIN
			      Formulations ON Configurations.ConfigurationID = Formulations.ConfigurationID INNER JOIN
	                      tblAuditActions ON Formulations.FormulationID = tblAuditActions.linkID INNER JOIN
	                      tblAuditActionType ON tblAuditActions.auditTypeID = tblAuditActionType.AuditTypeID
	WHERE     ((SELECT     COUNT(t1.LinkID)
	                         FROM         tblAuditActions t1
	                         WHERE     t1.LinkID = Formulations.FormulationID AND t1.auditTypeID < 5) = @level)
	AND tblAuditActions.auditTypeID = @level
	ORDER BY
		CASE
			WHEN @orderby = '1' THEN Configurations.PartNumber
			WHEN @orderby = '2' THEN Configurations.configType
			WHEN @orderby = '3' THEN Configurations.RevisionNumber
			WHEN @orderby = '4' THEN Formulations.MoldOption
			WHEN @orderby = '5' THEN Configurations.ODInch
			WHEN @orderby = '6' THEN Configurations.IDInch
			WHEN @orderby = '7' THEN tblAuditActions.auditDate
	        END
	DESC

This one almost works...but the date, #7 doesnt order properly because its a varchar. If i cast again as sdt, it will order properly, but then 1 - 6 fail with error above.

DECLARE @level int, @orderby varchar(1)
SET @level = 3
SET @orderby = '6'
SELECT  Configurations.PartNumber,Configurations.configType,
	Configurations.RevisionNumber,Formulations.MoldOption, Configurations.ODInch,
	Configurations.IDInch, tblAuditActions.auditDate,
	       (SELECT t2.AuditTypeDesc FROM tblAuditActionType t2 WHERE t2.auditTypeID = tblAuditActions.auditTypeID + 1)As AuditTypeDesc, 
		tblAuditActions.auditTypeID
	FROM         Configurations INNER JOIN
			      Formulations ON Configurations.ConfigurationID = Formulations.ConfigurationID INNER JOIN
	                      tblAuditActions ON Formulations.FormulationID = tblAuditActions.linkID INNER JOIN
	                      tblAuditActionType ON tblAuditActions.auditTypeID = tblAuditActionType.AuditTypeID
	WHERE     ((SELECT     COUNT(t1.LinkID)
	                         FROM         tblAuditActions t1
	                         WHERE     t1.LinkID = Formulations.FormulationID AND t1.auditTypeID < 5) = @level)
	AND tblAuditActions.auditTypeID = @level
	ORDER BY
		CASE
			WHEN @orderby = '1' THEN CONVERT(varchar(50),Configurations.PartNumber)
			WHEN @orderby = '2' THEN CONVERT(varchar(50),Configurations.configType)
			WHEN @orderby = '3' THEN CONVERT(varchar(50),Configurations.RevisionNumber)
			WHEN @orderby = '4' THEN CONVERT(varchar(50),Formulations.MoldOption)
			WHEN @orderby = '5' THEN CONVERT(varchar(50),Configurations.ODInch)
			WHEN @orderby = '6' THEN CONVERT(varchar(50),Configurations.IDInch)
			WHEN @orderby = '7' THEN CONVERT(varchar(50),tblAuditActions.auditDate)
	        END
	DESC

If i only convert #7, then a couple more options fail.

Why is the order by setting the data type? when i use @orderby as an int, it states error converting to datatype int.

Thanks for any input for my output!

Adam

 

[Nagrom]

I'm not sure about why the ORDER BY is setting the data type, but I have a suggestion on how to get around it. If you take your query and load the results into a temporary table you can then generate and execute dynamic SQL to return the data ordered as you like. Basically it would be something like this:

============================

declare @dynamicSQL varchar(50)

create table #TempUnorderedData (
Field1 varchar(50),
Field2 int,
Field3 smalldatetime
)

insert into #TempUnorderedData
select [the rest of your query to get values for the 3 fields]

set @dynamicSQL = 'select * from #TempUnorderedData (nolock) order by ' + CASE... END (<--this is where you'd put the field name for the ordering)

exec @dynamicSQL

==========================

This would still allow the optimizer to work on the query that actually retrieves the data (which you wouldn't get if you made the whole thing dynamic) while circumventing the issue with the dynamic ORDER BY clause. As a side note, you do have to use a temporary table for this solution (as opposed to a table variable) because a table variable would not be available to the "exec" statement.

 

[adamroof]

wouldnt there be performance issues recreating the temp table over and over each time this was executed?? At what point would the temp table drop?

 

[Nagrom]

Assuming this code is in a stored procedure, you would want to drop the temp table as soon as you are done working with the data. If all you are doing is returning the data then you would drop the table immediately after the exec statement. If you don't drop it explicitly the table would drop when the stored proc falls out of scope, but it's always a good idea to drop temporary tables explicitly as soon as you are done with them to free resources. As to performance issues for creating the table, a lot of it depends on the volume of data you are working with. In the example above I'm explicitly creating the temporary table with a "create table" statement (as opposed to the "select... into" syntax, which creates the table dynamically). This will help out performance as far as record locking and the like, but the degree of savings all depends on exactly what you're querying (volume of data, indexes on the data, etc). I can say from experience that I've used a query like this that returned around 14,000 records and didn't see a significant performance degradation, but again a lot of that is dependent on the data you're working with and the data structure.

 

[gmmastros]

What are the corresponding data types for the following fields?

Configurations.PartNumber
Configurations.configType
Configurations.RevisionNumber
Formulations.MoldOption
Configurations.ODInch
Configurations.IDInch
tblAuditActions.auditDate

I was messing around with this a little.

Set NOCOUNT ON
Declare @OrderColumn Integer
Declare @Temp Table (Id Integer, DateColumn DateTime, VarCharColumn VarChar(10))

Insert Into @Temp Values(1,  '2001-01-01', 'Fred')
Insert Into @Temp Values(41, '2002-01-01', 'Wilma')
Insert Into @Temp Values(12, '2002-10-01', 'Barney')
Insert Into @Temp Values(18, '2002-03-01', 'Dino')
Insert Into @Temp Values(20, '2001-01-01', 'Bam-Bam')
Insert Into @Temp Values(6,  '2001-01-01', 'Betty')

Set @OrderColumn = 1

Select Id,
       DateColumn,
       VarCharColumn
From   @Temp
Order By Case When @OrderColumn=1 Then Right(Replicate('0',20) + Convert(VarChar(20), ID), 20)
              When @OrderColumn=2 Then Convert(VarChar(10), DateColumn, 120)
              When @OrderColumn=3 Then VarCharcolumn
              End

This seems to work, but notice that I had to convert all 3 fields to the same data type. If I don't do this, then the query fails.

You could try changing...
WHEN @orderby = '7' THEN tblAuditActions.auditDate
To...
WHEN @orderby = '7' THEN Convert(int, tblAuditActions.auditDate)


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[adamroof]

Holy Moo!!!!

Your CONVERT(varchar(10),tblAuditActions.auditDate,120)
did it...

i was almost there, i was using 101 and 102, 110, couldnt find a complete listing of the dt conversions. May it was the varchar(10)

DECLARE @level int, @orderby varchar(1)
SET @level = 3
SET @orderby = '7'
SELECT  Configurations.PartNumber,Configurations.configType,
	Configurations.RevisionNumber,Formulations.MoldOption, Configurations.ODInch,
	Configurations.IDInch, tblAuditActions.auditDate,
	       (SELECT t2.AuditTypeDesc FROM tblAuditActionType t2 WHERE t2.auditTypeID = tblAuditActions.auditTypeID + 1)As AuditTypeDesc, 
		tblAuditActions.auditTypeID
	FROM         Configurations INNER JOIN
			      Formulations ON Configurations.ConfigurationID = Formulations.ConfigurationID INNER JOIN
	                      tblAuditActions ON Formulations.FormulationID = tblAuditActions.linkID INNER JOIN
	                      tblAuditActionType ON tblAuditActions.auditTypeID = tblAuditActionType.AuditTypeID
	WHERE     ((SELECT     COUNT(t1.LinkID)
	                         FROM         tblAuditActions t1
	                         WHERE     t1.LinkID = Formulations.FormulationID AND t1.auditTypeID < 5) = @level)
	AND tblAuditActions.auditTypeID = @level
	ORDER BY
		CASE
			WHEN @orderby = '1' THEN CONVERT(varchar(50),Configurations.PartNumber)
			WHEN @orderby = '2' THEN CONVERT(varchar(50),Configurations.configType)
			WHEN @orderby = '3' THEN CONVERT(varchar(50),Configurations.RevisionNumber)
			WHEN @orderby = '4' THEN CONVERT(varchar(50),Formulations.MoldOption)
			WHEN @orderby = '5' THEN CONVERT(varchar(50),Configurations.ODInch)
			WHEN @orderby = '6' THEN CONVERT(varchar(50),Configurations.IDInch)
			WHEN @orderby = '7' THEN CONVERT(varchar(10),tblAuditActions.auditDate,120)
	        END
	DESC

When i win the lottery, sql tek-tips pros like george are number 9 on my list....


To answer questions...

Configurations.PartNumber ---- int
Configurations.configType ---- varchar 50
Configurations.RevisionNumber -- varchar 2 (rev letter actually)
Formulations.MoldOption -- varchar 2
Configurations.ODInch -- decimal 9(13,7)
Configurations.IDInch -- decimal 9(13,7)
tblAuditActions.auditDate -- sdt

Convert(int,tblAuditActions.auditDate)
--- error on 2,3,5 and 6 converting to int


I use dynamic sql like nagrom suggest, but i just dont like the red colors... more over im afraid of its performance and error checking, but i'll start when i get more dynamic requests...

 

[gmmastros]

>> When i win the lottery, sql tek-tips pros like george are number 9 on my list....

Only 9?

To get a complete list of conversion numbers...

Type: Convert in to Query Analyzer.
Position the cursor on the word.
Press Shift-F1

This will open Books On Line.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[adamroof]

1. Payoff Bills
2. Unpaid Collections from 10 years ago
3. Mother inlaw (helped with my daughter)
4. Brother needs business capital
5. Start own business
6. Daughter to college
7. Long time friends all get new cars of choice
8. Start second business
9. tek-tips donation; pizza party for all (safe bet for those who might be vegetarians);couple of new servers for those unique individuals (sorry, no microsoft licensing, that would kill me)(might have to wait for second annual payment for that one)