On sorting invalid column name 'state'

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

SQL

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

Posted 6-Mar-16 22:39pm

itsathere

Updated 7-Mar-16 22:27pm

v2

Add a Solution

3 solutions

Solution 2

Small modification in column alias in CTE tempProfile. Try with below query:

SQL

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

Posted 6-Mar-16 23:45pm

Manas_Kumar

Updated 7-Mar-16 20:20pm

v2

Comments

itsathere 8-Mar-16 1:10am

I have already tried this is not working.

Andy Lanng 8-Mar-16 1:55am

There are a couple of ways to write column aliases. It makes no difference which one you choose

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2016-03-06T22:57:00

Solution 1

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:

SQL

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

Posted 6-Mar-16 22:57pm

Andy Lanng

Updated 7-Mar-16 22:28pm

v2

Comments

itsathere 8-Mar-16 1:03am

this query is not returning any data.

Andy Lanng 8-Mar-16 1:53am

try removing the "where" clause and see what data you get. There is nothing else in the query that would cause data to be omitted.
I'm guessing that you are trying to set up a paging query?

itsathere 8-Mar-16 2:11am

Yes, I am trying for paging with sorting in asp.net webforms.After removing where clause I am getting error incorrect syntax near '('

Andy Lanng 8-Mar-16 2:22am

Could you post the new stored proc with the Where clause removed please. There is no reason for that error with only the Where clause removed.

With paging you will need to sort by a secondary unique field. This is because I doubt that 'state' for eg is unique and therefore the sorting may be inconsistent. Use ROW_NUMBER() OVER (ORDER BY '+ @SortExpression+', ID ) AS RowNumber instead.

Andy Lanng 8-Mar-16 2:29am

Off to work - I'll pick this up then

itsathere 8-Mar-16 2:35am

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
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 '

end
EXEC sp_executesql @SqlString
END

Andy Lanng 8-Mar-16 4:02am

remove the second BEGIN and first END

itsathere 8-Mar-16 4:13am

Incorrect syntax near '('.

Andy Lanng 8-Mar-16 4:18am

missing a comma:
[commandname] ,

itsathere 8-Mar-16 4:19am

getting Incorrect syntax near '('.Ok, I am able to resolve error but what about paging if we remove where clause.

itsathere 8-Mar-16 4:26am

THANKS.

Andy Lanng 8-Mar-16 4:27am

That's just to test the code and find the errors. Put the clause back in now that we have fixed it

itsathere 8-Mar-16 4:29am

I have done that. Thank you.

Andy Lanng 8-Mar-16 4:30am

No worries. Glad it worked out
Don't forget: If any of those parameters are null then you will get no results.
in SQL: string + null + string = null

itsathere 8-Mar-16 4:35am

Table have foreign keys. Btw thanks again.

itsathere · Accepted Answer · 2016-03-07T22:27:00

SQL

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