在排序无效列名称'state' [英] On sorting invalid column name 'state'
本文介绍了在排序无效列名称'state'的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
ALTER PROCEDURE [dbo]。[Profile_GETCity]
@ PageSize int = null ，
@ CurrentPage int = null ，
@ SortExpression nvarchar （max）= null
AS
BEGIN
SET NOCOUNT ON
DECLARE @ SqlString nvarchar （max）
声明 @ UpperBand int
声明 @ LowerBand int
SET < span class =code-sdkkeyword> @ LowerBand =（ @ CurrentPage - 1 ） * @ PageSize
SET @ UpperBand =（ @ CurrentPage * @ PageSize ）+ 1
BEGIN
SET @ SqlString = ' WITH tempProfile AS
（
SELECT
[ ID]，
[Name]，
state =（从State_District选择Name，其中City.StateId = State_District.ID），
区=（从State_District选择Name，其中City.DistrictId = State_District.ID），
[PinCode]，
[状态] = CASE [IsActive] WHEN 1那么''标签标签-sm标签 - 成功''当0那么''标签标签-sm label-danger''END，
[commandname] = CASE [IsActive] WHEN 1，然后''有效''当0'然后''无效''结束，
ROW_NUMBER（）超过（订购' + @ SortExpression + ' ）AS RowNumber
FROM [dbo]。[City]
）
SELECT
[ID]，
[姓名]，
州，
区，
[PinCode]，
[状态]，
[命令名]
来自
tempProfil e
WHERE
RowNumber> ' + CONVERT （ VARCHAR ， @ LowerBand ）+ ' AND RowNumber<' + CONVERT（ VARCHAR ， @ UpperBand ）
+ ' ORDER BY' + @ SortExpression
end
EXEC sp_executesql @ SqlString
END
子查询列不是作为排序列读取
我尝试过：
此链接
解决方案
您无法在row_number中按州排序，因为它未在row_number列之前进行评估。在查询后应用别名。
您有两个选择：
在tempProfile cte之前创建另一个cte，首先执行别名分配：
SET @ SqlString = '
WITH
别名为（
SELECT
[ID]，
[Name]，
state =（从State_District中选择Name，其中City.StateId = State_District.ID），
区=（从State_District选择Name，其中City.DistrictId = State_District.ID），
[PinCode]，
[状态] = CASE [IsActive]
WHEN 1 THEN''label label-sm label-success''
WHEN 0 THEN''label label-sm label-danger''END，
[commandname] = CASE [IsActive]
WHEN 1 THEN''有效''
当0'那么'InActive''结束
来自[dbo]。[City]
），
tempProfile AS
（
SELECT
[ID]，
[姓名]，
州，
区，
[PinCode]，
[状态]，
[命令名]，
ROW_NUMBER（）OVER（ORDER BY' + @ SortExpression + ' ）AS RowNumber
FROM别名
）
SELECT
[ID]，
[姓名]，
州，
区，
[PinCode] ，
[状态]，
[命令名]
FROM
tempProfile
WHERE
RowNumber> ' + CONVERT （ VARCHAR ， @ LowerBand ）+ ' AND RowNumber<' + CONVERT（ VARCHAR ， @ UpperBand ）
+ ' ORDER BY' + @ SortExpression
或者在排序表达式中使用整个（从State_District中选择Name.StateId = State_District.ID中的Name）。但是，这并不适合你在这种情况下的使用。
希望有所帮助
Andy ^ _ ^
更新：错过了逗号
CTE中列别名的小修改tempProfile。尝试以下查询：
ALTER 程序 [dbo]。[Profile_GETCity]
@ PageSize int = null ，
@ CurrentPage int = null ，
@ SortExpression nvarchar （max） = null
AS
BEGIN
SET NOCOUNT ON
DECLARE @ SqlString nvarchar （max）
声明 @ UpperBand int
声明 @ LowerBand int
SET @ LowerBand =（ @ CurrentPage - 1 ）* @ PageSize
SET @ UpperBand =（ @ CurrentPage * @ PageSize ）+ 1
SET @ SqlString = ' WITH tempProfile AS
（
SELECT
[ID]，
[Name]，
（select来自State_District的名称，其中City.StateId = State_District.ID）AS状态，
（从State_District中选择Name，其中City.DistrictId = State_District.ID）AS区，
[PinCode]，
CASE [ IsActive]
WHEN 1那么'标签label-sm label-success '
当0那么'标签label-sm label-danger ' END AS status，
CASE [ IsActive]
WHEN 1 THEN'活动'
WHEN 0 THEN' InActive ' END AS commandname，
ROW_NUMBER（）OVER（ORDER BY' + @ SortExpression + ' ）AS RowNumber
FROM [dbo]。[City]
）
选择
[ID]，
[姓名]，
[州]，
[区]，
[PinCode]，
[状态]，
[commandname]
FROM
tempProfile
WHERE
RowNumber> ' + CONVERT （ VARCHAR ， @ LowerBand ）+ ' AND RowNumber<' + CONVERT（ VARCHAR ， @ UpperBand ）
+ ' ORDER BY' + @ SortExpression
EXEC sp_executesql @ SqlString
END
ALTER PROCEDURE [ dbo]。[Profile_GETCity]
@ PageSize int = null ，
@ CurrentPage int = null ，
@ SortExpression nvarchar （max）= null
AS
BEGIN
SET NOCOUNT ON
DECLARE @ SqlString nvarchar （max）
声明 @ UpperBand int
声明 @ LowerBand int
SET @ LowerBand =（ @ C urrentPage - 1 ）* @ PageSize
SET @ UpperBand =（ @ CurrentPage * @ PageSize ）+ 1
SET @ SqlString = '
WITH
别名为（
SELECT
[ID]，
[名称]，
州=（从State_District选择姓名，其中City.StateId = State_District.ID），
区=（从State_District中选择Name.DistrictId = State_District.ID），
[PinCode]，
[status] = CASE [IsActive]
WHEN 1 THEN''label label-sm label-success ''
WHEN 0 THEN''标签标签-sm label-danger''END，
[commandname] = CASE [IsActive]
WHEN 1 THEN''Active''
当0'那么'InActive''结束
来自[dbo]。[City]
），
tempProfile AS
（
SELECT
[ID]，
[名称]，
州，
区，
[PinCode ]，
[状态]，
[命令名]，
ROW_NUMBER（）OVER（ORDER BY' + @ SortExpression + ' ，ID）AS RowNumber
FROM别名
）
SELECT
[ID]，
[姓名]，
州，
区，
[PinCode]，
[状态]，
[命令名]
FROM
tempProfile WHERE
RowNumber> ' + CONVERT （ VARCHAR ， @ LowerBand ）+ ' AND RowNumber<' + CONVERT（ VARCHAR ， @ UpperBand ）
+ ' ORDER BY' + @ SortExpression
end
EXEC sp_executesql @ SqlString
ALTER PROCEDURE [dbo].[Profile_GETCity ]
@PageSize int = null,
@CurrentPage int = null,
@SortExpression nvarchar(max) = null
AS
BEGIN
SET NOCOUNT ON
DECLARE @SqlString nvarchar(max)
Declare @UpperBand int
Declare @LowerBand int
SET @LowerBand = (@CurrentPage - 1) * @PageSize
SET @UpperBand = (@CurrentPage * @PageSize) + 1
BEGIN
SET @SqlString='WITH tempProfile AS
(
SELECT
[ID],
[Name],
state=(select Name from State_District where City.StateId=State_District.ID),
District=(select Name from State_District where City.DistrictId=State_District.ID),
[PinCode],
[status] = CASE [IsActive] WHEN 1 THEN ''label label-sm label-success'' WHEN 0 THEN ''label label-sm label-danger'' END,
[commandname] = CASE [IsActive] WHEN 1 THEN ''Active'' WHEN 0 THEN ''InActive'' END ,
ROW_NUMBER() OVER (ORDER BY '+ @SortExpression+' ) AS RowNumber
FROM [dbo].[City]
)
SELECT
[ID],
[Name],
state,
District,
[PinCode],
[status],
[commandname]
FROM
tempProfile
WHERE
RowNumber > ' + CONVERT(VARCHAR,@LowerBand) + 'AND RowNumber <' +CONVERT(VARCHAR, @UpperBand)
+ 'ORDER BY ' + @SortExpression
end
EXEC sp_executesql @SqlString
END
sub query column is not reading as a column on sorting
What I have tried:
this link
解决方案
You can't order by state in the row_number because it isn't assessed before the row_number column. The aliases are applied after the query.
You have two options:
Create another cte before the tempProfile cte that does the alias assignments first:
SET @SqlString=' WITH aliases as ( SELECT [ID], [Name], state=(select Name from State_District where City.StateId=State_District.ID), District=(select Name from State_District where City.DistrictId=State_District.ID), [PinCode], [status] = CASE [IsActive] WHEN 1 THEN ''label label-sm label-success'' WHEN 0 THEN ''label label-sm label-danger'' END, [commandname] = CASE [IsActive] WHEN 1 THEN ''Active'' WHEN 0 THEN ''InActive'' END FROM [dbo].[City] ), tempProfile AS ( SELECT [ID], [Name], state, District, [PinCode], [status], [commandname], ROW_NUMBER() OVER (ORDER BY '+ @SortExpression+' ) AS RowNumber FROM aliases ) SELECT [ID], [Name], state, District, [PinCode], [status], [commandname] FROM tempProfile WHERE RowNumber > ' + CONVERT(VARCHAR,@LowerBand) + 'AND RowNumber <' +CONVERT(VARCHAR, @UpperBand) + 'ORDER BY ' + @SortExpression
Or use the entire(select Name from State_District where City.StateId=State_District.ID)in the sort expression. That won't really suit your use in this case, though.
Hope that helps
Andy ^_^
UPDATE: Missed a comma
Small modification in column alias in CTEtempProfile. Try with below query:
ALTER PROCEDURE [dbo].[Profile_GETCity ] @PageSize int = null, @CurrentPage int = null, @SortExpression nvarchar(max) = null AS BEGIN SET NOCOUNT ON DECLARE @SqlString nvarchar(max) Declare @UpperBand int Declare @LowerBand int SET @LowerBand = (@CurrentPage - 1) * @PageSize SET @UpperBand = (@CurrentPage * @PageSize) + 1 SET @SqlString='WITH tempProfile AS ( SELECT [ID], [Name], (select Name from State_District where City.StateId=State_District.ID) AS state, (select Name from State_District where City.DistrictId=State_District.ID) AS District, [PinCode], CASE [IsActive] WHEN 1 THEN 'label label-sm label-success' WHEN 0 THEN 'label label-sm label-danger' END AS status, CASE [IsActive] WHEN 1 THEN 'Active' WHEN 0 THEN 'InActive' END AS commandname, ROW_NUMBER() OVER (ORDER BY '+ @SortExpression+' ) AS RowNumber FROM [dbo].[City] ) SELECT [ID], [Name], [state], [District], [PinCode], [status], [commandname] FROM tempProfile WHERE RowNumber > ' + CONVERT(VARCHAR,@LowerBand) + 'AND RowNumber <' +CONVERT(VARCHAR, @UpperBand) + 'ORDER BY ' + @SortExpression EXEC sp_executesql @SqlString END
ALTER PROCEDURE [dbo].[Profile_GETCity] @PageSize int = null, @CurrentPage int = null, @SortExpression nvarchar(max) = null AS BEGIN SET NOCOUNT ON DECLARE @SqlString nvarchar(max) Declare @UpperBand int Declare @LowerBand int SET @LowerBand = (@CurrentPage - 1) * @PageSize SET @UpperBand = (@CurrentPage * @PageSize) + 1 SET @SqlString=' WITH aliases as ( SELECT [ID], [Name], state=(select Name from State_District where City.StateId=State_District.ID), District=(select Name from State_District where City.DistrictId=State_District.ID), [PinCode], [status] = CASE [IsActive] WHEN 1 THEN ''label label-sm label-success'' WHEN 0 THEN ''label label-sm label-danger'' END, [commandname] = CASE [IsActive] WHEN 1 THEN ''Active'' WHEN 0 THEN ''InActive'' END FROM [dbo].[City] ), tempProfile AS ( SELECT [ID], [Name], state, District, [PinCode], [status], [commandname] , ROW_NUMBER() OVER (ORDER BY '+ @SortExpression+', ID ) AS RowNumber FROM aliases ) SELECT [ID], [Name], state, District, [PinCode], [status], [commandname] FROM tempProfile WHERE RowNumber > ' + CONVERT(VARCHAR,@LowerBand) + 'AND RowNumber <' +CONVERT(VARCHAR, @UpperBand) + 'ORDER BY ' + @SortExpression end EXEC sp_executesql @SqlString
这篇关于在排序无效列名称'state'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
