Numeric ordering on varchar based co-related subqueries
let me say that i've a table with some generic-data-holder columns (so it seems developer of the original code selected to use string type). These columns can hold all numerics, alphanumerics or a mix of them.
Let me say i've a co-related subquery like;
SOME_CORELATE开发者_开发技巧D_QUERY=(SOME SELECT SQL)
and let's say that i've an order by clause;
order by ID,SOME_CORELATED_QUERY
now as these fields are all varchar based, i can't get them numerically sorted by default.
I can try this for my co-related subquery;
SOME_CORELATED_QUERY=cast((SOME SELECT SQL) as int)
but the problem with above is that, not all my data is numeric and i'll get an error;
Conversion failed when converting the varchar value '304L' to data type int.
So i found a solution here which basicly uses spaces which is viable for my problem but the problem is that i can't apply it to co-related subquery.
If i try to apply it on my table column ID, it all works okay;
order by space(50-len(ID))+ID, SOME_CORELATED_QUERY
but that doesn't work as a solution because i need a numeric sort on my SOME_CORELATED_QUERY. I try it like;
order by space(50-len(SOME_CORELATED_QUERY))+SOME_CORELATED_QUERY
but i get an error message because somewhat my co-related subquery is not recognized by SQL functions like space,cast, convert etc:
Msg 207, Level 16, State 1, Line 48
Invalid column name 'SOME_CORELATED_QUERY'.
Msg 207, Level 16, State 1, Line 48
Invalid column name 'SOME_CORELATED_QUERY'.
So basically i need way to use SQL function space,cast,convert on my co-related subquery on sort-time.
Note: Changing the database layout, field-type is not possible as the layout is hard-coded on the program i use, which i'm just creating SQL based reports for user.
- I'm also working on mssql2005.
Update: Thanks to Brad, i updated the question text to use 'co-related subqueries' as it was a better description than virtual columns.
Update2: Here's the full-sql as it's asked for;
select ID, S.STOCK_ID ,S.STOCK_CODE,S.STOK_SHORT_NAME,S.UNIT1_NAME UNIT_NAME,
PROPERTY_VALUE_NAME1=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_1),
PROPERTY_NAME1=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT, STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY1_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME2=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_2),
PROPERTY_NAME2=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY2_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME3=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_3),
PROPERTY_NAME3=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY3_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME4=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_4),
PROPERTY_NAME4=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY4_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME5=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_5),
PROPERTY_NAME5=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY5_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME6=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_6),
PROPERTY_NAME6=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY6_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
PROPERTY_VALUE_NAME7=(Select PROPERTY_VALUE from STOCK_PROPERTY_VALUES where PROPERTY_VALUE_ID=a.PROPERTY_VALUE_ID_7),
PROPERTY_NAME7=(Select PROPERTY_NAME from STOCK_PROPERTY_DEFINITIONS SOT,STOCK_PROPERTY_TEMPLATES SOS where SOS.PROPERTY7_ID=SOT.PROPERTY_ID and SOS.ID=a.ID),
isnull(SUM(case IN_OUT when 1 then UNIT1_VALUE else 0 end),0) IN_VALUE,
isnull(SUM(case IN_OUT when 2 then UNIT1_VALUE else 0 end),0) OUT_VALUE
from STOCK_PROPERTY_DETAILS a, STOCK b, STOCK_TRANSACTION_ROWS DHS, STOCK S, WAREHOUSE D
where a.STOCK_ID=b.STOCK_ID and b.STOCK_ID *= DHS.SHD_ID and DHS.SHD = 1 and ID>0 and B.UPPER_STOCK_ID=S.STOCK_ID and D.WAREHOUSE_ID*=DHS.WAREHOUSE_ID
group by S.STOCK_ID ,S.STOCK_CODE,S.STOK_SHORT_NAME,S.UNIT1_NAME, PROPERTY_VALUE_ID_1,PROPERTY_VALUE_ID_2,PROPERTY_VALUE_ID_3,PROPERTY_VALUE_ID_4,PROPERTY_VALUE_ID_5,PROPERTY_VALUE_ID_6,PROPERTY_VALUE_ID_7,ID
having 1=1 and ((isnull(SUM(case IN_OUT when 1 then UNIT1_VALUE else 0 end),0)-isnull(SUM(case IN_OUT when 2 then UNIT1_VALUE else 0 end),0)<>0 /*##ZERO#*/) or (not(1=1 /*##ZERO#*/)))
order by ID,PROPERTY_VALUE_NAME1,PROPERTY_VALUE_NAME2,PROPERTY_VALUE_NAME3,PROPERTY_VALUE_NAME4,PROPERTY_VALUE_NAME5,PROPERTY_VALUE_NAME6,PROPERTY_VALUE_NAME7
And i want to sort on PROPERTY_VALUE_NAME1, PROPERTY_VALUE_NAME2 and so..
I can't test it as I don't have an instance to hand but if it will allow you to use a case statement on it something like this may work
order by
case
when isnumeric(columnname) then cast(columnname as int)
else -1
end,
columnname
Hopefully this would do it in two sorts, the first sorting numeric values in numeric order and lumping the alpha stuff in a group at -1 which would then get sorted by alpha by our second part
For numeric ordering on character data use ORDER BY LEN(columnName), columnName
.
精彩评论