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
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
ystem.Data.OracleClient.OracleConnection, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089