ORDER BY, CASE, with multiple columns

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks

SELECT ...
ORDER BY (CASE Lower(@.SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

Also, i realize that in your order by statement, when you use CASE, all of your columns have to be the same data type.

So in ORDER BY clause above, i am attempting to order by "prodprice" as one of the possibilities. This produces the error:Error converting data type varchar to numeric.

The whole IDEA of a case statement is to avoid opening yourself to injection attacks by Dynamic Execution.

So...how can you use the case statement to order by multiple columns, and to order with different datatypes?|||Not sure it this helps butview post 386101 discusses something close to your question. Maybe the method discussed near the bottom can be adapted.|||Right, so you'd have something like this:

SET @.SortExpression = Lower(@.SortExpression)
SELECT ...
ORDER BY
CASE WHEN @.SortExpression = 'prodname' THEN prodname END,
CASE WHEN @.SortExpression = 'prodname' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodsize END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodname END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodname END,
prodcompany,
prodname

I'm not exactly sure what that'll do to performance. It'd be worth it to see what the execution plan says.

Terri|||that alleviated some of my problem. Thanks.

How exactly do i test the excecution plan? or check to see if it's compiling all the way?

Can i use a SQL Trace?|||Check out this article:SQL Server Query Execution Plan Analysis.

Terri

Oracle reports using SQL Reporting Services

Hi! I am using SQL Reporting Services to create reports on data that is
stored on an Oracle server. I am unable to use query parameters when
accessing Oracle (i.e. @.ID). Is there an Oracle equivalent that I can
use in Reporting Services? Any help would be greatly appreciated.
Just to clarify, it's something similar to this (larger scale, of
course):
SELECT FirstName, LastName
FROM EmployeeInformation
WHERE EmployeeID = @.ID
Whenever I create a query parameter, Reporting Services tells me that I
am missing an expression...The managed Oracle provider (data source type: "Oracle") uses ":" instead of
"@." to identify parameters. Try this instead:
SELECT FirstName, LastName
FROM EmployeeInformation
WHERE EmployeeID = :ID
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nandan" <nandanrp@.gmail.com> wrote in message
news:1128455599.396906.96420@.g47g2000cwa.googlegroups.com...
> Hi! I am using SQL Reporting Services to create reports on data that is
> stored on an Oracle server. I am unable to use query parameters when
> accessing Oracle (i.e. @.ID). Is there an Oracle equivalent that I can
> use in Reporting Services? Any help would be greatly appreciated.
> Just to clarify, it's something similar to this (larger scale, of
> course):
> SELECT FirstName, LastName
> FROM EmployeeInformation
> WHERE EmployeeID = @.ID
> Whenever I create a query parameter, Reporting Services tells me that I
> am missing an expression...
>|||Yes that did it! Thanks!!!

Oracle Provider of OLEDB - Unable to process cubes

Hi all,

Our data mart resides in Oracle 9i.

I can connect the data source views fine, and explore the data in those views.

Even in the cubes themselves, I can explore the data.

But when I go to process the cubes, I get the error ...

"Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'RTM Data Mart', Name of 'RTM Data Mart'.

Anyone know why?

The provider is the Native OLE DB provider from Oracle.

Thanks,

--Phil

Check your impersonation settings, it should be 'use the service account'|||

That did the trick - thanks.

Interesting that the Data Source Views are happy enough with a fixed identity, but processing cubes wants the service account.

But I am happy to have made progress - thanks again.