SELECT re.*, rtl.*, CAST(RIGHT(IsNull(PubNumber,'77-0'), LEN(IsNull(PubNumber,'77-0'))-3) as int) as NumberWithinAYear FROM Research re, ResearchTypesLookup rtl WHERE re.ResearchTypeID=2 --AND re.ResearchYear=1998 AND re.ResearchTypeID=rtl.ResearchTypeID AND NOT CONTAINS(re.PubNumber, 'SPECIAL') ORDER BY re.ResearchYear, re.ResearchDate, NumberWithinAYear
UNION SELECT re.*, rtl.*, 0 as NumberWithinAYear FROM Research re, ResearchTypesLookup rtl WHERE re.ResearchTypeID=2 --AND re.ResearchYear=1998 AND re.ResearchTypeID=rtl.ResearchTypeID AND CONTAINS(re.PubNumber, 'SPECIAL') ORDER BY re.ResearchYear, re.ResearchDate, re.PubNumber KC> Hello, everyone. I posted this question on the SQL listserv, KC> but things are pretty quiet over there. I'm hoping someone over KC> here can help me with one of my queries. I'm working with a SQL KC> Server table that has "PubNumber" as one of its varchar columns. KC> PubNumber *typically* is a hybrid of the last two digits of the KC> calendar year plus another number, such as "05-8", "00-2", KC> "98-10", etc. I'm trying to order my items by PubNumber, but I'm KC> getting results such as the following: KC> 98-1 KC> 98-10 KC> 98-2 KC> 98-3 KC> etc. KC> As you might have guessed, I want 98-10 coming after 98-9 and KC> not 98-1. So, I thought I'd be creative with the following: KC> SELECT *, CAST(LEFT(PubNumber, 2) as int) + '-' + KC> CAST(RIGHT(PubNumber, LEN(PubNumber)-3) as int) KC> FROM Research re, ResearchTypesLookup rtl KC> WHERE re.ResearchTypeID=2 KC> AND re.ResearchYear=1998 KC> AND re.ResearchTypeID=rtl.ResearchTypeID KC> ORDER BY PubNumber, ResearchDate, ResearchTitle KC> Works great UNTIL PubNumber is either blank/null OR contains KC> some lovely anomaly like "SPECIAL". So, I tried my hand at some KC> IF/ELSE and CASE statements and couldn't get anything to work. KC> Any thoughts? I can't turn this into a stored procedure -- I need KC> to just keep it in the CF code. Thanks in advance. KC> Kamie KC> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:205909 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
