Re: a SQL Statement Error - Database Discussion Boards

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

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

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

Try this[^]

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

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.

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

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:

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.

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:

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

Thank you Niladri
it helped

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

Great explanation for a question in the database forum.

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

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

Can you post how you resolved it?

Naina

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

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?

Mugdha_Aditya wrote:
Please help me as soon as possible

Start here[^] Smile | :)

I are Troll Suspicious | :suss:

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

Can i know the reason why you want to use Pivot?

Using For XML PATH('') also you can get the same result.

select emp_name + ', ' from tabble where condition for xml path('')

Regards
Naina

Naina

in my data base in the order table there is a status field and it has many different values relevant to orders and order table has a relationship with customers table on customercode, what i want is to get the count of each order status relevant to each customer.any idea how

Look into the group by clause.

Select count(*), Customer, Status
From Table
Group By Customer, Status

Never underestimate the power of human stupidity
RAH

I want to display a schedule in a Gantt chart, MS Project-like, horizontal stacked bar chart.

I embedded an unbound OLE object on my form, selected MS Graph Chart, and defined Row Source = qry_MySched, Row Source Type = Table/Query.

What I am seeing is my rows/columns are transposed and I a missing a row of data that should display.

Here is my data:

tbl_Schedule  (ID, StartDay, Duration)
1, 1, 3
2, 6, 5
3, 7, 4

qry_MySched (SQL format)

SELECT "Event " & [ID] AS Label, [StartDay]-1 AS [Begin], tbl_Schedule.Duration
FROM tbl_Schedule;

Resulting in the query:

Event 1, 0, 3
Event 2, 5, 5
Event 3, 6, 4

My stacked bar chart then has
(Y-axis): 3, 0
(legend): Event 2, Event 3
(horizontal bar for "3") 0-5 (5units), Event2; 5-9 (4units), Event 3
(horizontal bar for "0") 0-5 (5units), Event2; 5-11 (6units), Event 3

Working with MS Graph Chart difficult in Access 2007. (Access 2007, WinXP, SP3)

Thanks for any help,
JJ

Hi..

I have the scene like this....
I have one master table say Users(userid, pwd) and child table customer(userid,name,address)
both tables are related with field 'userid'.

Now customer wants to update his userid.
so userid will be updated in master as well as child record.
could u suggest me the update query for this without deleting and inserting the records.

thanks,
Hemant

Your basic design is wrong, the userid should not be significant and should not be the primary key if it can be changed. However, if this is an existing system which you cannot change then simply do the update to both tables within a single transaction - assuming you do not have a foreign key constraint.

Bob
Ashfield Consultants Ltd