Order By Parameter

[digimortal]

Can not we use parameters (as fieldnames) in Reporting services?

I'm putting a combobox and field names in it and let the user select the order type of the report

for example:

Select * from X order by @param
where param is a field name.

BTW I'm connecting to an Oracle DB so the format must be:
Select * from X order by ?

 

[ookete]

Hmmm, perhaps not. I don't believe you can use variables (or parameters) as field names in SQL Server either. I don't know Oracle but what I would do in SQL Server if I needed a dynamic "ORDER BY" clause, is make a stored procedure that accepts your @param and then builds an ad-hoc query. Something like:

CREATE PROCEDURE pr_MyProcedure
      @param varchar(10)
AS

DECLARE @str varchar(100)
SET @str = 'SELECT * FROM MyTable ORDER BY ' + @param

EXEC(@str)

So that last line executes the on-the-fly string that you made. In SQL Server there is also something called Dynamic SQL, but I don't know if Oracle has anything like that. It's pretty much the same principle as above.

Good luck.

 

[digimortal]

Problem solved..

.
.
.
	(X.LANGU = 'T') AND 
	(X.CLIENT = C.CLIENT) AND 
	(X.COMPANY = C.COMPANY) AND
	(X.ACCOUNT = C.CUSTOMER) AND 
	(C.COUNTRY = 'TR') AND 
	(C.ADVPACC <> '120-15') AND 
	(C.ISDELETE =0) 
GROUP BY I.CLIENT, I.COMPANY, I.ACCTYPE, A.ACCIND, I.ACCOUNT, X.STEXT
ORDER BY 
case when ? = 'STEXT' AND ?='Art' Then STEXT ELSE NULL END ASC,
case when ? = 'STEXT' AND ?='Azal' Then STEXT ELSE NULL END DESC,
case when ? = 'MT'  AND ?='Art' Then MT ELSE NULL END , STEXT ASC,
case when ? = 'MT'  AND ?='Azal' Then MT  ELSE NULL END , STEXT DESC,
case when ? = 'ACCOUNT'  AND ?='Art' Then ACCOUNT ELSE NULL END ASC,
case when ? = 'ACCOUNT'  AND ?='Azal' Then ACCOUNT ELSE NULL END DESC,
case when ? = 'BAKIYE'  AND ?='Art' Then BAKIYE ELSE NULL END ASC,
case when ? = 'BAKIYE'  AND ?='Azal' Then BAKIYE ELSE NULL END DESC