Saj Saj - 6 days ago 4
SQL Question

SQL - How to refer alias when using CASE within CTE - Datatable Server-side query

I am writing a Stored Procedure in MS SQL to get data from multiple table using CASE for a JQuery datatable.

This is my query

USE [DELTONECRM-LIVE]
GO

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.

Comments