Saj Saj - 9 days ago 8
SQL Question

SQL Server : how to refer alias when using CASE within CTE - datatable server-side query

I am writing a stored procedure in SQL Server to get data from multiple table using

CASE
for a jQuery datatable.

This is my query

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[getAllQuotes]
@iDisplayLength int,
@iDisplayStart int,
@SortCol int,
@SortDir nvarchar(10),
@Search nvarchar(255),
@Status nvarchar(20) = NULL
AS
BEGIN
Declare @FirstRec int, @LastRec int

Set @FirstRec = @iDisplayStart;
Set @LastRec = @iDisplayStart + @iDisplayLength;

WITH CTE_Quotes as
(
SELECT
ROW_NUMBER() OVER (Order by case when (@SortCol = 0 and @SortDir = 'asc')
then QuoteID
end asc,
case when (@SortCol = 0 and @SortDir = 'desc')
then QuoteID
end desc,
case when (@SortCol = 1 and @SortDir = 'asc')
then QuoteDateTime
end asc,
case when (@SortCol = 1 and @SortDir = 'desc')
then QuoteDateTime
end desc,
case when (@SortCol = 2 and @SortDir = 'asc')
then CustomerType
end asc,
case when (@SortCol = 2 and @SortDir='desc')
then CustomerType
end desc,
case when (@SortCol = 3 and @SortDir='asc')
then CompanyName
end asc,
case when (@SortCol = 3 and @SortDir='desc')
then CompanyName
end desc,
case when (@SortCol = 4 and @SortCol='asc')
then ContactName
end asc,
case when (@SortCol = 4 and @SortCol='desc')
then ContactName
end desc,
case when (@SortCol = 5 and @SortCol='asc')
then Total
end asc,
case when (@SortCol = 5 and @SortCol='desc')
then Total
end desc,
case when (@SortCol = 6 and @SortCol='asc')
then QuoteBy
end asc,
case when (@SortCol = 6 and @SortCol='desc')
then QuoteBy
end desc,
case when (@SortCol = 7 and @SortCol='asc')
then Status
end asc,
case when (@SortCol = 7 and @SortCol='desc')
then Status
end desc
)
as RowNum,
COUNT(*) over() AS TotalCount,
QuoteID, QuoteDateTime, CustomerType =
CASE
WHEN Flag = 'QuoteDB' THEN 'New Customer'
WHEN Flag = 'LiveDB' THEN 'Existing Customer'
END , CompanyName =
CASE
WHEN Flag = 'QuoteDB' THEN (SELECT CompanyName FROM dbo.Quote_Companies QCP WHERE QCP.CompanyID = QT.CompanyID)
WHEN Flag = 'LiveDB' THEN (SELECT CompanyName FROM dbo.Companies CP WHERE CP.CompanyID = QT.CompanyID)
END,
ContactName =
CASE
WHEN Flag = 'QuoteDB' THEN (SELECT FirstName + ' ' + LastName FROM dbo.Quote_Contacts QCC WHERE QCC.ContactID = QT.ContactID)
WHEN Flag = 'LiveDB' THEN (SELECT FirstName + ' ' + LastName FROM dbo.Contacts CT WHERE CT.ContactID = QT.ContactID)
END,
Total, QuoteBy, Status
FROM dbo.QUOTE QT
WHERE (@Search IS NULL
Or QuoteID LIKE '%' + @Search + '%'
or QuoteDateTime LIKE '%' + @Search + '%'
or CustomerType LIKE '%' + @Search + '%'
or CompanyName LIKE '%' + @Search + '%'
or ContactName LIKE '%' + @Search + '%'
or Total LIKE '%' + @Search + '%'
or QuoteBy LIKE '%' + @Search + '%'
or Status LIKE '%' + @Search + '%'
)
AND Status = @Status
)
SELECT *
FROM CTE_Quotes
WHERE RowNum > @FirstRec AND RowNum < @LastRec


end

However I get the following errors when trying to create it


Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 94

Invalid column name 'CustomerType'.

Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 95

Invalid column name 'CompanyName'.

Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 96

Invalid column name 'ContactName'.

Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 37

Invalid column name 'CustomerType'.

Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 40

Invalid column name 'CustomerType'.

Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 43

Invalid column name 'CompanyName'.

Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 46

Invalid column name 'CompanyName'.

Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 49

Invalid column name 'ContactName'.

Msg 207, Level 16, State 1, Procedure getAllQuotes, Line 52

Invalid column name 'ContactName'.


Can anyone please let me know how I go about fixing those errors?

I am not sure how to go about referencing aliases.

Thanks

Answer

use cross apply to introduce the CompanyName and other expressions and you'll be able to reference them from the other clauses.

as an aside you do not have to use a row_number() in a cte to page your results if you have sql server 2012 or newer. You can use offset-fetch with your whole case statement in the order by clause.