April 30, 2009 at 12:24 pm
Ok... I've been banging my head against this one all morning and I admit I am stumped. I am developing a report in SSRS with the goal of a "user defined sort order". I have done this previously by passing the contents of a param to the Order By clause... no big deal.
However, this particular report references "date" data stored in the DB as varchar strings which obvioulsy do not ORDER correctly (chronologically). So, I am converting the strings to datetime in the view which supports the report.
The problem is when I set the @sort param to anything but one of the date fields I receive the Conversion failed when converting datetime from character string..
If I run the query with the @Param set to one of the fields I am converting to datetime, the query executes successfully.
Is this a problem with using the case in the ORDER BY clause? Thanks for any advice...
Here's the query:
select
B1_ALT_ID, B1_APPL_STATUS, B1_SPECIAL_TEXT, (case when Jurisdiction is NULL then 'No Jurisdiction' else Jurisdiction end) "Jurisdiction", Location, "Total Platted Lots", "Total Remaining Lots", "Reservation of Capacity Approved", "ROC CONDITIONS DATE", "Build Out Years", convert(money, "APF Payment Per Lot") "APF Payment Per Lot", "APF Payment Due At", "Total Approved Lots", "Annexation Jurisdiction", "Annexation Date", "DO APPROVED DATE", "CA APPROVED DATE", "CA RECORDED DATE", "MA APPROVED DATE", "MA RECORDED DATE", "AA APPROVED DATE", "AA RECORDED DATE",
"2000 Permits",
"2001 Permits",
"2002 Permits",
"2003 Permits",
"2004 Permits",
"2005 Permits",
"2006 Permits",
"2007 Permits",
"2008 Permits",
"2009 Permits",
"2010 Permits"
from rpvwCabarrusAPF1
where (b1_special_text = @subdivision or 1=case when @subdivision = 'all' then 1 end)
and (jurisdiction = @jurisdiction or 1=case when @jurisdiction = 'all' then 1 end)
order by
case upper(@Sort)
when 'SUBDIVISION' then B1_SPECIAL_TEXT
when 'APF NUMBER' then B1_ALT_ID
when 'STATUS' then B1_APPL_STATUS
when 'PAYMENT DUE' then "APF Payment Due At"
when 'PAYMENT PER LOT' then "APF Payment Per Lot"
when 'ROC APPROVED' then "Reservation of Capacity Approved"
when 'DEV ORDER APPROVED' then "DO APPROVED DATE"
when 'CA APPROVED' then "CA APPROVED DATE"
when 'CA RECORDED' then "CA RECORDED DATE"
when 'MA APPROVED' then "MA APPROVED DATE"
when 'MA RECORDED' then "MA RECORDED DATE"
when 'AA APPROVED' then "AA APPROVED DATE"
when 'AA RECORDED' then "AA RECORDED DATE"
when 'ANNEX DATE' then "Annexation Date"
when 'ANNEX JURISDICTION' then "Annexation Jurisdiction"
else ' ' end
April 30, 2009 at 12:44 pm
From Books Online (page on CASE)
Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.
All of the columns you specify in the CASE must be able to convert to whichever has the highest precedence. Apparently that's datetime. Try converting all the expressions in the CASE to varchar, just with a dateformat that sorts 'correctly' for the datetime ones (like 20090430, format 112 or 121)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 30, 2009 at 1:12 pm
Thanks Gail... for lifting my head above the weeds. I was totally ignoring disparate data types in my CASE. I modified both the view and the CASE in the Order By clause as suggested and the report is sorting "chronologically" and Alphabetically as needed.
thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply