August 1, 2007 at 9:30 am
Hi guys n gals.. I have a proc that runs fine in 2000 but fails in 2005 to even compile with this error:
Msg 408, Level 16, State 1, Procedure adv_spGetTransactions_TEST, Line 356
A constant expression was encountered in the ORDER BY list, position 5.
This is the order by mechanism ( @Sortby is passed in and defaulted to NULL)
Any Ideas?
select * from @results2
ORDER BY
--TopLevel Sort
CASE @SortBy
WHEN 'SaleDate' THEN convert(varchar(10),TradeDate,102)
WHEN 'AccountName' THEN AccountName
WHEN 'Symbol' THEN Ticker
WHEN 'SecDesc'THEN Description
WHEN NULL THEN Description
ELSE NULL
END ASC,
CASE @SortBy
WHEN 'Quantity'THEN Quantity
WHEN 'OrigPrice' THEN OriginalPrice
WHEN 'OrigTC'THEN OriginalCost
WHEN 'SalePrice' THEN UnitPrice
WHEN 'SaleTotal' THEN SalesProceed
WHEN 'RGL' THEN RGLAmount
WHEN 'RGLPct'THEN RGLPct
ELSE NULL
END DESC,
--Second Level Sort
CASE @SortBy
WHEN 'Symbol'THEN CUSIP
WHEN 'SaleDate' THEN Ticker
WHEN 'SecDesc' THEN NULL
WHEN NULL THEN convert(varchar(10),TradeDate,102)
ELSE Ticker
END ASC,
--Third Level Sort
CASE @SortBy
WHEN 'SaleDate' THEN convert(varchar(10),AcquisitionDate,102)
ELSE NULL
END ASC,
--non Quantity must be on its own sort
<--THIS IS THE PROBLEM BELOW---->
CASE @sortBy
WHEN NULL THEN Quantity
ELSE NULL
END
August 1, 2007 at 4:09 pm
ORDER BY
--TopLevel Sort
CASE
WHEN @SortBy = 'SaleDate' THEN convert(varchar(10),TradeDate,102)
WHEN @SortBy = 'AccountName' THEN AccountName
WHEN @SortBy = 'Symbol' THEN Ticker
WHEN @SortBy = 'SecDesc' THEN Description
WHEN @SortBy IS NULL THEN Description
ELSE NULL
END ASC,
CASE @SortBy
WHEN 'Quantity' THEN Quantity
WHEN 'OrigPrice' THEN OriginalPrice
WHEN 'OrigTC' THEN OriginalCost
WHEN 'SalePrice' THEN UnitPrice
WHEN 'SaleTotal' THEN SalesProceed
WHEN 'RGL' THEN RGLAmount
WHEN 'RGLPct' THEN RGLPct
ELSE NULL
END DESC,
--Second Level Sort
CASE
WHEN @SortBy = 'Symbol' THEN CUSIP
WHEN @SortBy = 'SaleDate' THEN Ticker
WHEN @SortBy = 'SecDesc' THEN NULL
WHEN @SortBy IS NULL THEN convert(varchar(10),TradeDate,102)
ELSE Ticker
END ASC,
--Third Level Sort
CASE @SortBy
WHEN 'SaleDate' THEN convert(varchar(10),AcquisitionDate,102)
ELSE NULL
END ASC,
--non Quantity must be on its own sort
CASE
WHEN @SortBy IS NULL THEN Quantity
ELSE NULL
END
N 56°04'39.16"
E 12°55'05.25"
August 2, 2007 at 6:49 am
Thanks,I ended up doing
Case @Sortby
When isnull(@sortby,Quantity) Then Quantity
Else Null
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply