a SQL Statement Error

Majid Shahabfar

.
.
.
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber

FROM	aspnet\_Users INNER JOIN aspnet\_Membership ON aspnet\_Users.UserId = aspnet\_Membership.UserId INNER JOIN
                  PD\_Users INNER JOIN PD\_Cities ON PD\_Users.CityID = PD\_Cities.CityId ON aspnet\_Users.UserName = PD\_Users.UserName
WHERE (@LastName = PD\_Users.LastName  OR (@LastName = '' AND 1=1))    
      AND (@UserName = PD\_Users.UserName OR (@UserName = '' AND 1=1))
      AND (@CityID = PD\_Users.CityID OR (@CityID = -1 AND 1=1))
      AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))

.
.
.

Error: Invalid name column 'IsOnline' (in Where statement) How could I solve it? thanks

Not Active

Obviously IsOnline is not a column in the table. Find out what the column name should be

I know the language. I've read a book. - _Madmatt

David Mujica

Is the UserStatus a string ? Then the IsOnline should be 'IsOnline' (in quotes). Or is IsOnline a SQL Server function that you have written ? Maybe it needs a parameter.

Majid Shahabfar

Yes I know and there is not the same column name in the table but I have to compare it with @UserStatus

Majid Shahabfar

@UserStatus is declared as bit and IsOnline is not a SQL function.

Not Active

Try this[^]

I know the language. I've read a book. - _Madmatt

Lost User

It's not legal to filter on a column that doesn't exist before the selection

select 1 as bla
where bla <> 1

This will generate an exception. You'd need to change the condition in your where-clause. Alternatively, you could use a view without the filter, and filter that using a second query. I'm thinking that IsOnline would be a good candidate to become a computed column.

I are Troll :suss:

Majid Shahabfar

this is the full SQL

@PageNumber int,
@PageSize int,
@MinutesSinceLastInActive int,
@CurrentTimeUtc datetime,
@LastName nvarchar(64),
@UserName nvarchar(256),
@CityID int,
@UserStatus bit

AS
BEGIN

DECLARE @FirstRow INT,
@LastRow INT,
@TotalRecords INT,
@DateActive datetime
SELECT @DateActive = DATEADD(minute, -(@MinutesSinceLastInActive), @CurrentTimeUtc)

SELECT @FirstRow = 1 + (@PageNumber * @PageSize),
@LastRow = @PageSize + @FirstRow - 1 ;

SELECT @TotalRecords = (SELECT COUNT(*) FROM PD_Users WHERE @LastName = PD_Users.LastName OR (@LastName = '' AND 1=1));

WITH Members AS
(
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber

FROM	aspnet\_Users INNER JOIN aspnet\_Membership ON aspnet\_Users.UserId = aspnet\_Membership.UserId INNER JOIN
                  PD\_Users INNER JOIN PD\_Cities ON PD\_Users.CityID = PD\_Cities.CityId ON aspnet\_Users.UserName = PD\_Users.UserName
WHERE (@LastName = PD\_Users.LastName  OR (@LastName = '' AND 1=1))    
      AND (@UserName = PD\_Users.UserName OR (@UserName = '' AND 1=1))
      AND (@CityID = PD\_Users.CityID OR (@CityID = -1 AND 1=1))
      AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))

)

SELECT RowNumber, FirstName, LastName, UserName, CityID, CityName, CreateDate,
ArticleCount,NewsCount, ITNewsCount, MessageCount, IsOnline
FROM Members
WHERE RowNumber BETWEEN @FirstRow AND @LastRow AND (@UserStatus = IsOnline)
ORDER BY RowNumber ASC

RETURN @TotalRecords
END

because I use this stored procedure for paging so I need to use IsOnline in first Select statement.

Niladri_Biswas

As far as Sql Server is concern, this you cannot do but the same can be done in Oracle. What you do is like

AND (@UserStatus = (CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end) OR (@UserStatus = 0 AND 1=1))

Hope this helps :)

Niladri Biswas

Lost User

A bit dirty, but turning it into a subquery should work too;

SELECT @FirstRow = 1 + (@PageNumber * @PageSize),
@LastRow = @PageSize + @FirstRow - 1 ;

SELECT @TotalRecords = (SELECT COUNT(*) FROM PD_Users WHERE @LastName = PD_Users.LastName OR (@LastName = '' AND 1=1));

WITH Members AS
(
select * from
(
SELECT PD_Users.FirstName as FirstName, PD_Users.LastName as LastName, PD_Users.UserName as UserName, PD_Users.CityID as CityID,
PD_Cities.CityName as CityName, aspnet_Membership.CreateDate as CreateDate,
(SELECT COUNT(*) FROM PD_Articles WHERE (PD_Users.UserName = UserName)) AS ArticleCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 1)) AS NewsCount,
(SELECT COUNT(*) FROM PD_News WHERE (PD_Users.UserName = SenderName AND IsDevNews = 0)) AS ITNewsCount,
(SELECT COUNT(*) FROM PD_ForumMessage WHERE (PD_Users.UserName = UserName)) AS MessageCount,
CASE WHEN aspnet_Users.LastActivityDate > @DateActive THEN cast(1 as bit) ELSE cast(0 as bit) end AS IsOnline,
ROW_NUMBER() OVER (ORDER BY aspnet_Membership.CreateDate ) AS RowNumber

FROM	aspnet\_Users INNER JOIN aspnet\_Membership ON aspnet\_Users.UserId = aspnet\_Membership.UserId INNER JOIN
                  PD\_Users INNER JOIN PD\_Cities ON PD\_Users.CityID = PD\_Cities.CityId ON aspnet\_Users.UserName = PD\_Users.UserName

) as TempCTE
WHERE (@LastName = PD_Users.LastName OR (@LastName = '' AND 1=1))
AND (@UserName = PD_Users.UserName OR (@UserName = '' AND 1=1))
AND (@CityID = PD_Users.CityID OR (@CityID = -1 AND 1=1))
AND (@UserStatus = IsOnline OR (@UserStatus = 0 AND 1=1))
)

SELECT RowNumber, FirstName, LastName, UserName, CityID, CityName, CreateDate,
ArticleCount,NewsCount, ITNewsCount, MessageCount, IsOnline
FROM Members
WHERE RowNumber BETWEEN @FirstRow AND @LastRow AND (@UserStatus = IsOnline)
ORDER BY RowNumber ASC

I are Troll :suss:

Majid Shahabfar

Thank you Niladri it helped