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.