CODEWITHSUNDEEP
sql-server
Stekoa
Stekoa

Reputation: 1

How do I make a dynamic order by clause when using calculated results and table aliases

I have the following query and can't get it to work when I replace the order by [Mach No] with parameters to make it a dynamic clause. I have read several posts about using Case and when and replacing the calculated name with the actual calculation but I feel that the problem relies on the table aliases which are necessary to get the calculations to work. It is a self joined table.

Select  D1.Gaming_day As [Gaming Day], D1.mach_id AS [Mach No], (D1.grs_coin_in-D2.grs_coin_in)/100 AS [Coin In],
        (D1.grs_drop-D2.grs_drop)/100 as [Drop],
         (D1.grs_cashable_ticket_out_amt-D2.grs_cashable_ticket_out_amt)/100 AS [Vouchers Out],
        (D1.grs_jackpot-d2.grs_jackpot)/100 AS Jackpots,
        (D1.grs_hpay_cashout_receipt_amt/100-D2.grs_hpay_cashout_receipt_amt/100) AS Handpay,
        ((D1.grs_drop-D2.grs_drop) - (D1.grs_cashable_ticket_out_amt-D2.grs_cashable_ticket_out_amt)-(D1.grs_jackpot-d2.grs_jackpot))/100 As [Win],
         Hold_Percentage/100 AS PAR,((D1.grs_coin_in-D2.grs_coin_in)*Hold_Percentage/100)/100 AS [Win at PAR],
        ((D1.grs_bills_in_1-D2.grs_bills_in_1)+(D1.grs_bills_in_2-D2.grs_bills_in_2)*2+(D1.grs_bills_in_5-D2.grs_bills_in_5)*5+(D1.grs_bills_in_10-D2.grs_bills_in_10)*10+(D1.grs_bills_in_20-D2.grs_bills_in_20)*20+(D1.grs_bills_in_50-D2.grs_bills_in_50)*50+(D1.grs_bills_in_100-D2.grs_bills_in_100)*100) AS  Currency,
        (d1.grs_cashable_ticket_in_amt-D2.grs_cashable_ticket_in_amt)/100 AS [Vouchers In] 
from DropMeters AS D1,DropMeters AS D2,MachineInformation 
where D1.gaming_day=(@date1)AND D2.gaming_day=(@date2) AND D1.mach_id=D2.mach_id AND D1.mach_id=MachineInformation.mach_id

Order by [Mach No]

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

When you want a dynamic order by, the safest thing to do is a separate case for each possible variable:

order by (case when @param = 'col1' then col1 end),
         (case when @param = 'col2', then col2 end),
         . . .

The reason is simple. The case expression returns a single value defined at compile time to have a particular type. The various rules on type conversion might result in an inappropriate conversion or error when the code runs.

Upvotes: 1

Related Questions