Database

Majid Shahabfar19-Feb-10 9:05

19-Feb-10 9:05

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

Not Active19-Feb-10 11:30

Not Active

19-Feb-10 11:30

Try this[^]

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

David Mujica19-Feb-10 6:04

David Mujica

19-Feb-10 6:04

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 Shahabfar19-Feb-10 9:06

19-Feb-10 9:06

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

Eddy Vluggen20-Feb-10 0:33

Eddy Vluggen

20-Feb-10 0:33

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

SQL

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 Suspicious | :suss:

Majid Shahabfar20-Feb-10 9:57

20-Feb-10 9:57

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.

Eddy Vluggen21-Feb-10 1:45

Eddy Vluggen

21-Feb-10 1:45

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

SQL

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 Suspicious | :suss:

Niladri_Biswas20-Feb-10 18:09

Niladri_Biswas

20-Feb-10 18:09

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
Smile | :)

Niladri Biswas

Majid Shahabfar21-Feb-10 10:17

21-Feb-10 10:17

Thank you Niladri
it helped

Error.

bhavnvyas18-Feb-10 23:34

bhavnvyas

18-Feb-10 23:34

Hi all
i am getting this error in my web applicacion.can any one help me how to resolve this error.plz

distributed transaction completed.either enlist this session in new transection or the null transaction

thnx in advance...

Re: Error.

Richard MacCutchan19-Feb-10 4:03

Richard MacCutchan

19-Feb-10 4:03

Great explanation for a question in the database forum.

txtspeak is the realm of 9 year old children, not developers. Christian Graus

Re: Error.

bhavnvyas19-Feb-10 18:20

bhavnvyas

19-Feb-10 18:20

thnx for help....
but i already solved this issue.....

Re: Error.

nainakarri21-Feb-10 22:29

nainakarri

21-Feb-10 22:29

Can you post how you resolved it?

Naina

PIVOT

Mugdha_Aditya18-Feb-10 22:47

Mugdha_Aditya

18-Feb-10 22:47

I have a table with only one column as

Emp_Name
A
B
C
D
E

I want to display this data as

Emp_Name
A, B, C, D, E

Please help me as soon as possible

JHizzle18-Feb-10 23:57

JHizzle

18-Feb-10 23:57

I'm not sure I understand, could you elaborate please? It looks like you're wanting to merge every record in a table into one? That's sort of anti-database design, no?

Eddy Vluggen20-Feb-10 6:10

Eddy Vluggen

20-Feb-10 6:10

Mugdha_Aditya wrote:
Please help me as soon as possible

Start here[^] Smile | :)

I are Troll Suspicious | :suss:

Niladri_Biswas20-Feb-10 17:42

Niladri_Biswas

20-Feb-10 17:42

Try this

declare @t table(Emp_Name varchar(50))
insert into @t select 'A' union all select 'B' union all select 'C'
union all select 'D' union all select 'E'

Solution 1:
declare @concat varchar(100)
set @concat = ''
select @concat = @concat + Emp_Name + ',' from @t
select Emp_Name =Left(@concat,len(@concat)-1)

Solution 2:
select Emp_Name = RIGHT(delimitedCol,len(delimitedCol)-1) from
(select ',' + CAST(Emp_Name AS varchar(8000))
from @t
for xml path(''))x(delimitedCol)

Output(For both the cases)

Emp_Name A,B,C,D,E

Even you can use while loop , cursors but the approaches mentioned above are faster enough.
Smile | :)

Niladri Biswas