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

ORDER BY Clause with multiple tables

Hi All
I am having a problem with an ORDER BY clause when selecting information from multiple tables. Eg
SELECT i.InvoiceId, pd.PayDescription, u.UserName
FROM Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId
LEFT OUTER JOIN tblUsers ON i.UserId = u.UserId
ORDER BY pd.PayDescription
this is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?
I am writing this for MSSQL Server 2000
Thanks
BraidenYou can definitely do an ORDER BY like that. What's the problem? Are you receiving an error?
(The abbreviated example you've supplied would of course return anerror because there is no definitition for the pd alias in the query.)
|||

Hi
This is my current stored procedure, it is implementing a server side paging algorithm.
--CODE BEGINS
CREATE PROCEDURE [dbo].[ap_APPInvoiceSearchSEL]

--Declare input parameters
@.ActivityInvoiceTypeID as int,
@.CommunityVisitorID as int,
@.PayRunID as bigint,
@.InvoiceDateFrom as datetime,
@.InvoiceDateTo as datetime,
@.PayStatusID as int,
@.PageNum as bigint,
@.PageSize as bigint,
@.SortExpression as varchar(50)

AS
BEGIN

-- Create a Variable Table to hold search results in
DECLARE @.SearchResults TABLE
(
SearchResultID bigint IDENTITY,
InvoiceID bigint,
SiteName varchar(50),
ActivityInvoiceTypeDescription varchar(50),
CommunityVisitorName varchar(100),
InvoiceDate datetime,
ActivityDate datetime,
PayStatusDescription varchar(50),
PayRunID bigint,
PayRunDate datetime,
ActivityAmountClaimed decimal(9),
TravelAmountClaimed decimal(9),
VehicleAllowanceClaimed decimal(9),
TotalAmountClaimed decimal(9),
RecordCount bigint

)

--Declare variables
DECLARE @.RecordCount as bigint,
@.StartRecord as bigint,
@.EndRecord as bigint

--Find the number of results
SELECT @.RecordCount = Count(i.InvoiceID)
FROM vwInvoice i LEFT OUTER JOIN tblCommunityVisitor c ON i.CommunityVisitorID = c.CommunityVisitorID
LEFT OUTER JOIN tblJAGUser u ON u.UserID = c.UserID
LEFT OUTER JOIN tluActivityInvoiceType it ON i.ActivityInvoiceTypeID = it.ActivityInvoiceTypeID
LEFT OUTER JOIN tluPayStatus ps ON i.PayStatusID = ps.PayStatusID
LEFT OUTER JOIN tblPayRun pr ON i.PayRunID = pr.PayRunID
LEFT OUTER JOIN tblSite s ON i.SiteID = s.SiteID

WHERE (i.ActivityInvoiceTypeID = @.ActivityInvoiceTypeID OR @.ActivityInvoiceTypeID is null)
AND (i.CommunityVisitorID = @.CommunityVisitorID OR @.CommunityVisitorID is null)
AND (i.PayRunID = @.PayRunID OR @.PayRunID is null)
AND (i.InvoiceDate >= @.InvoiceDateFrom OR @.InvoiceDateFrom is null)
AND (i.InvoiceDate <= @.InvoiceDateTo OR @.InvoiceDateTo is null)
AND (i.PayStatusID = @.PayStatusID OR @.PayStatusID is null)

INSERT INTO
@.SearchResults (InvoiceID,
SiteName,
ActivityInvoiceTypeDescription,
CommunityVisitorName,
InvoiceDate,
ActivityDate,
PayStatusDescription,
PayRunID,
PayRunDate,
ActivityAmountClaimed ,
TravelAmountClaimed,
VehicleAllowanceClaimed,
TotalAmountClaimed,
RecordCount)

SELECT i.InvoiceID,
s.SiteName,
it.ActivityInvoiceTypeDescription,
u.FirstName + ' ' + u.LastName as CommunityVisitorName,
i.InvoiceDate as InvoiceDate,
i.ActivityDate,
ps.PayStatusDescription,
i.PayRunID,
pr.PayRunDate,
i.ActivityAmountClaimed,
i.TravelAmountClaimed,
i.VehicleAllowanceClaimed,
i.TotalAmountClaimed,
@.RecordCount As RecordCount

FROM vwInvoice i LEFT OUTER JOIN tblCommunityVisitor c ON i.CommunityVisitorID = c.CommunityVisitorID
LEFT OUTER JOIN tblJAGUser u ON u.UserID = c.UserID
LEFT OUTER JOIN tluActivityInvoiceType it ON i.ActivityInvoiceTypeID = it.ActivityInvoiceTypeID
LEFT OUTER JOIN tluPayStatus ps ON i.PayStatusID = ps.PayStatusID
LEFT OUTER JOIN tblPayRun pr ON i.PayRunID = pr.PayRunID
LEFT OUTER JOIN tblSite s ON i.SiteID = s.SiteID

WHERE (i.ActivityInvoiceTypeID = @.ActivityInvoiceTypeID OR @.ActivityInvoiceTypeID is null)
AND (i.CommunityVisitorID = @.CommunityVisitorID OR @.CommunityVisitorID is null)

AND (i.PayRunID = @.PayRunID OR @.PayRunID is null)
AND (i.InvoiceDate >= @.InvoiceDateFrom OR @.InvoiceDateFrom is null)
AND (i.InvoiceDate <= @.InvoiceDateTo OR @.InvoiceDateTo is null)
AND (i.PayStatusID = @.PayStatusID OR @.PayStatusID is null)

ORDER BY

CASE @.SortExpression
WHEN 'InvoiceID' THEN i.InvoiceID
WHEN 'SiteName' THEN s.SiteName
WHEN 'ActivityInvoiceTypeDescription' THEN it.ActivityInvoiceTypeDescription
WHEN 'CommunityVisitorName' THEN u.FirstName
WHEN 'ActivityDate' THEN i.ActivityDate
WHEN 'PayStatusDescription' THEN ps.PayStatusDescription
WHEN 'ActivityAmountClaimed' THEN i.ActivityAmountClaimed
WHEN 'TravelAmountClaimed' THEN i.TravelAmountClaimed
WHEN 'VehicleAllowanceClaimed' THEN i.VehicleAllowanceClaimed
WHEN 'TotalAmountClaimed' THEN i.totalAmountClaimed
ELSE i.InvoiceID
END

--Determine page positions
SET @.StartRecord = ((@.PageNum-1) * @.PageSize) + 1
SET @.EndRecord = @.PageNum * @.PageSize

--Now get the page of search results from the temp table
SELECT *
FROM @.SearchResults
WHERE SearchResultID BETWEEN @.StartRecord AND @.EndRecord

END
GO
--CODE ENDS
After further investigation i have narrowed it down to the final select case statement, which works find when the @.SortExpression variables resolves to a field that is in the invoice table. However when it does not i get a rather perculiar error saying"Syntax error converting datetime from character string."
Thanks
Braiden

|||Hi,
I observed that the ORDER BY works successfully when the order by column is one of the following types: int, boolean, datetime
But when the order by column is varchar or nvarchar, the sql statement fails with the following error message
Syntax error converting datetime from characterstring.

declare @.column nvarchar(100)
set @.column = 'firstname'
select *
from Customers
order by
case @.column
when 'cityid' then cityid
when 'firstname' then firstname
when 'birthdate' then birthdate
when 'active' then active
end
Eralper
http://www.kodyaz.com

|||Hi,
I realised that the problem occurs if your case statement has at leaston different column type side by side with varchar or nvarchar datatype.
If you do not have a column with data type string, the order by with case runs successfully.
Or in the case statement if you have only varchar data types (no othertype like datetime, boolean, int, etc) then it runs again successfully.

CASE @.SortExpression
WHEN 'InvoiceID' THEN i.InvoiceID
WHEN 'SiteName' THEN s.SiteName
WHEN 'ActivityInvoiceTypeDescription' THEN it.ActivityInvoiceTypeDescription
WHEN 'CommunityVisitorName' THEN u.FirstName
WHEN 'ActivityDate' THEN i.ActivityDate
WHEN 'PayStatusDescription' THEN ps.PayStatusDescription
WHEN 'ActivityAmountClaimed' THEN i.ActivityAmountClaimed
WHEN 'TravelAmountClaimed' THEN i.TravelAmountClaimed
WHEN 'VehicleAllowanceClaimed' THEN i.VehicleAllowanceClaimed
WHEN 'TotalAmountClaimed' THEN i.totalAmountClaimed
ELSE i.InvoiceID
END
If you really need a functionality like this, you should use run a copy of the script for string data types

Or you can use dynamic sql statements
Eralper
|||Hi again,
You can use multiple CASE statement in ORDER BY instead of one
declare @.column nvarchar(100)
set @.column = 'firstname'
select *
from Customers
order by
case when @.column = 'cityid' then cityid end,
case when @.column = 'firstname' then firstname end

You can check the article at http://www.extremeexperts.com/SQL/Articles/CASEinORDER.aspx

|||Thanks aloteralper
That was a big help, i have got it working now
Braiden

Order by clause

If I use the order by clause to sort on a date, where the date and
time stamp are the exact same for multiple records, how does SQL
output the data?
At random... or does it look at the primary key?"Chirag Patel" <romeo9225@.yahoo.com> wrote...
> If I use the order by clause to sort on a date, where the date and
> time stamp are the exact same for multiple records, how does SQL
> output the data?
> At random... or does it look at the primary key?

Generally speaking these behaviors are considered "undefined". As such you
cannot rely on it even if it "appears" to always do the same thing and if
you absolutely need the set ordered then you have to include it in the order
clause.

oracle pl/sql multiple spool help required

Now Im not an expert or anything with SQL so bear with me...

I have 3 seperate queries that each use the spool command to write to a file on the server.
e.g. i use the following construct in each of the three sql files:

set blah
set blah
spool /blah/blah.rpt
script
spool off

I wish to put these three queries into one script. Can I use the spool command three times in the one file? i.e. have spool then spool off, three times in one *.sql file?

I have read a couple of posts on pl/sql spooling and people mention utl_file, but I have no idea what this is and even if i have it...

Any help would be great. Thanks.Yes, you can. For example,

spool a1.txt
select count(*) from tab;
spool off;

spool a2.txt
select sysdate from dual;
spool off;

spool a3.txt
select 'x' dummy from dual;
spool off;

will generate 3 .txt files. However, I can't figure out why didn't you try it yourself ...|||I did try it and it didnt work the way I expected...

I have the three spools and also I have a title for each of the three files:

e.g.
ttitle 'Thanet ** Items made MISSING between 7 and 14 days ago' skip 2

I have one of these fore each script [different txt of course]. I have found that if the query returns no results it will not print the title to the file and so i have an emty file. If there are some results from on of the queries then the title does display.

I was wanting to make sure that I was using the spool correctly so thats why i asked.|||Perhaps this helps ... if you include a "dummy" query into every "spool block", you won't get an empty file even though your "real" query returns no records.

SPOOL a1.txt
TTITLE 'First top title' skip 2
BTITLE 'First bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;

SELECT COUNT (*) FROM tab;
SPOOL off;

SPOOL a2.txt
TTITLE 'Second top title' skip 2
BTITLE 'Second bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;

-- this query returns no rows
SELECT 'x' FROM dual WHERE sysdate = sysdate + 1;
SPOOL off;|||thanks for the help, ill give that a try.