Some tips:
* definitely use a CASE statement...
* use isnumeric(LEFT(PubNumber, 2) to see if you get a valid number
before doing the cast (or maybe do that in the WHERE clause, if that
makes sense)
* if you put the case expression as the first column in the select then
you can do "ORDER BY 1", which saves you from having to duplicate the
code (and that assumes you need to return that field... If you done,
just put the CASE in the ORDER BY).
* it looks like you were trying to rebuild PubNumber from it's parts
(left + "-" + right)... If you need the original PubNumber then just
return it as-is
Hope that helps...
Mark
-----Original Message-----
From: Kamie Curfman [mailto:[EMAIL PROTECTED]
Sent: Friday, May 06, 2005 3:02 PM
To: CF-Talk
Subject: SQL IF/ELSE or CASE Statement Help
Hello, everyone. I posted this question on the SQL listserv, but things
are pretty quiet over there. I'm hoping someone over here can help me
with one of my queries. I'm working with a SQL Server table that has
"PubNumber" as one of its varchar columns. PubNumber *typically* is a
hybrid of the last two digits of the calendar year plus another number,
such as "05-8", "00-2", "98-10", etc. I'm trying to order my items by
PubNumber, but I'm getting results such as the following:
98-1
98-10
98-2
98-3
etc.
As you might have guessed, I want 98-10 coming after 98-9 and not 98-1.
So, I thought I'd be creative with the following:
SELECT *, CAST(LEFT(PubNumber, 2) as int) + '-' + CAST(RIGHT(PubNumber,
LEN(PubNumber)-3) as int) FROM Research re, ResearchTypesLookup rtl
WHERE re.ResearchTypeID=2
AND re.ResearchYear=1998
AND re.ResearchTypeID=rtl.ResearchTypeID
ORDER BY PubNumber, ResearchDate, ResearchTitle
Works great UNTIL PubNumber is either blank/null OR contains some lovely
anomaly like "SPECIAL". So, I tried my hand at some IF/ELSE and CASE
statements and couldn't get anything to work. Any thoughts? I can't
turn this into a stored procedure -- I need to just keep it in the CF
code. Thanks in advance.
Kamie
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:205893
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
