November 16, 2007 at 6:42 am
I have written a stored procedure (Sql Server 2005) which acts as an advanced search engine for a PDM system we are developing. I have ran into a major roadblock and am hoping someone may have ran into (and resolved) this before as well.
Inevidably, I display a a result set to the user consisting of columns which have been generated dynamically (and pivoted). The problem is that I cannot seem to figure out how to get sorting working correctly. I beleive I have narrow the problem down to the culprit: The case statement I am using to retrieve the column values displayed to the user.
Here is the scenario:
A part may or may not have attributes.
Examples:
An electrical part may have voltage tolerances, heat tolerances, etc
A Mechanical part may have Inside/Outside diameters, Material Type, etc
When the user launches my advanced search screen (web based: asp .net 2.0) and selects a part type, I dynamically create a screen which gives them the ability to perform a search based on the attributes specific to that part type. They can fill out a some fields, all fields, the left side of a range, the right side of a range, or both sides of a range (This is only part of the available search critera, but it's the complicated part). Once the user clicks submit, I build a where-clause dynaically in the code behind, and then submit it to a stored procedure as a parameter. The stored procedure also uses dynamic SQL since I cannot be sure what columns are going to be involved in the query. Finally, after the entire query is created and executed, the result set has to pivoted and then returned to the search result screen for display to the user.
All of this appears to be working fine with one exception: The ability to sort the pivoted results correctly. The columns are sorted as text and not as the proper data type (ie: 200, 1, 4, 300 sorts as if alphabetic: 1, 200, 300, 4) . I think I have narrowed the problem down to a case statement used to retreive the value for a part type and cannot figure out how to resolved it.
The values for these part attributes are stored as different data types. In the table, the value can be in one of four columns:
varchar - for text
int- for even numbers
real- for floating point integers
bit- for boolean values
The value for a part type can only be in only one of the value columns. Here is an example:
---------------------------------------------------------------------------------------------------------------
PartNumberAttributeCodeTextAttributeValueIntegerAttributeValueNumericAttributeValueBooleanAttributeValue
---------------------------------------------------------------------------------------------------------------
123456XXXXXX2134.123
234567YYYYYYDescription
345678ZZZZZZ19
In order to extract and display a single value, I am using a case statement like:
SELECT CASE WHEN (TextAttributeValue IS NOT NULL)
THEN (TextAttributeValue)
WHEN (IntegerAttributeValueIS NOT NULL)
THEN Convert(varchar(max), CAST(IntegerAttributeValue as Decimal))
WHEN (NumericAttributeValue IS NOT NULL)
THEN Convert(varchar(max), CAST(NumericAttributeValue as Decimal))
ELSE NULL
END
I believe my issue is due to the fact that I am casting them to varchar but cannot seem to figure out a way around it. Sql Server complains if the columns are not of the same data type. Changing the table layout isn't an option either. I have also tried using the DataFormatString property of the BoundField in gridview control, but the field is ignored since the results are already a string (from Sql Server) If anyone has any suggestions, I would really appreciate it. I am hoping it's something trivial that I am overlooking.
- Jason
November 16, 2007 at 7:36 am
I am assuming that you could have an alpha character in the column. This being the case, if you want the semi-numeric sorting you can pad spaces to the beginning of everything.
" 2" comes before " 10".
CASE WHEN (TextAttributeValue IS NOT NULL) THEN RIGHT(SPACE(50) + (TextAttributeValue),50)
WHEN (IntegerAttributeValue IS NOT NULL) THEN RIGHT(SPACE(50) + Convert(varchar(), CAST(IntegerAttributeValue as Decimal)),50)
WHEN (NumericAttributeValue IS NOT NULL) THEN RIGHT(SPACE(50) + Convert(varchar(), CAST(NumericAttributeValue as Decimal)),50)
ELSE NULL
You don't need to use this as your returned column, just in your ORDER BY clause. This way, if you have a truncation problem or don't want the padding, it is not actually in your returned result set (although it may mess up your order).
You may want to go a step further and pad more spaces on fields that are numeric, ensuring that all of your numeric values come before anything that has an alpha character (pretty standard for semi-numeric sorting).
November 16, 2007 at 8:40 am
Thanks for the quick reply !
This may be a clever/easy workaround.
My first reaction was: why didn't I think of that ?
thanks again, I really appreciate it.
-Jason
November 16, 2007 at 11:41 am
I think at some point, the mind decides that everything MUST be complicated just because everything to that point has been.
November 16, 2007 at 12:24 pm
I think I ran into a snag. Negative numbers.
Any ideas ?
November 18, 2007 at 9:24 pm
well... you could add 99999999(...) to the number value in the order by to make all number positive, that would be the simplest way.
Paul Ross
November 19, 2007 at 5:15 am
That would probably work, or you could put the "-" before the padding.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply