Hello, everyone. I'm having problems 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 code. Thanks in advance.
Kamie
Yahoo! Mail
Stay connected, organized, and protected. Take the tour:
http://tour.mail.yahoo.com/mailtour.html
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2283
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
