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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy