sp help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp help

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2009-04-16 : 09:52:36

I have a sp I"m working with that someone else wrote. Anyway, I am trying to add a where clause which continues to give me the following error msg: Invalid column name 'Status'. The sp works fine except for the fact I can't add the where clause. Here is the sp:

SELECT S.SectionID,convert(varchar, R.Date,1) as aDate, R.Sequence, R.Title
,Pages = SUM(PageCount),
Status = (SELECT Name FROM tblStatus WHERE StatusID =
(SELECT MIN(StatusID) FROM tblSectionStatus SS
WHERE SS.SectionID = S.SectionID))
FROM tblReports R
INNER JOIN tblS .... blah blah blah
Where Status = null

Does anyone know what I should be using in the where clause? I also tried using Name, but that gives me the same error - :(

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-16 : 09:57:50

ann
Posting Yak Master

220 Posts

Posted - 2009-04-16 : 10:03:35

No that doesn't work at all - here is the full sp:

SELECT S.SectionID,convert(varchar, R.Date,1) as aDate, R.Sequence, R.Title
,Pages = SUM(PageCount),
Status = (SELECT Name FROM tblStatus WHERE StatusID =
(SELECT MIN(StatusID) FROM tblSectionStatus SS
WHERE SS.SectionID = S.SectionID))
FROM tblReports R
INNER JOIN tblSections S ON S.ReportID = R.ReportID
INNER JOIN tblSectionPages SP ON SP.SectionID = S.SectionID
WHERE R.Date BETWEEN @frDt AND @toDt
AND Status = null
GROUP BY S.SectionID, R.Date, R.Sequence, R.Title
ORDER BY R.Date DESC, R.Title, R.Sequence

END

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-16 : 10:14:34

Have you tried it?

declare @tbl as table(cnt int)
insert into @tbl
select 1 union all
select 2 union all
select 3 union all
select 4

select cnt1=(select COUNT(*) from @tbl) from @tbl
where cnt1>10 will give an error but the following works

select * from(
select cnt1=(select COUNT(*) from @tbl) from @tbl
)t where cnt1>10

ann
Posting Yak Master

220 Posts

Posted - 2009-04-16 : 10:19:01

Yes I tried it - these are the error msgs I get:

Msg 8120, Level 16, State 1, Line 1
Column 'tblSections.SectionID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "S.SectionID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "R.Date" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "R.Sequence" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "R.Title" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "R.Date" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "R.Title" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "R.Sequence" could not be bound.

here is the modified sp based on your reply:

Select * from
(
SELECT S.SectionID,convert(varchar, R.Date,1) as aDate, R.Sequence, R.Title
,Pages = SUM(PageCount),
Status = (SELECT Name FROM tblStatus WHERE StatusID =
(SELECT MIN(StatusID) FROM tblSectionStatus SS
WHERE SS.SectionID = S.SectionID))
FROM tblReports R
INNER JOIN tblSections S ON S.ReportID = R.ReportID
INNER JOIN tblSectionPages SP ON SP.SectionID = S.SectionID
)t where Status = null
GROUP BY S.SectionID, R.Date, R.Sequence, R.Title
ORDER BY R.Date DESC, R.Title, R.Sequence

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-16 : 10:23:07

But the original error is not replicating
Try this
Select * from
(
SELECT S.SectionID,convert(varchar, R.Date,1) as aDate, R.Sequence, R.Title
,Pages = SUM(PageCount),
Status = (SELECT Name FROM tblStatus WHERE StatusID =
(SELECT MIN(StatusID) FROM tblSectionStatus SS
WHERE SS.SectionID = S.SectionID))
FROM tblReports R
INNER JOIN tblSections S ON S.ReportID = R.ReportID
INNER JOIN tblSectionPages SP ON SP.SectionID = S.SectionID
)t where Status = null
GROUP BY SectionID, Date, Sequence, Title
ORDER BY Date DESC, Title, Sequence

This time if some error comes please post the actual error message rather than simply saying its not working.

ann
Posting Yak Master

220 Posts

Posted - 2009-04-16 : 10:29:18

ok - running your query I get the following error msg:

Msg 8120, Level 16, State 1, Line 1
Column 'tblSections.SectionID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 10:31:14

quote:
Originally posted by ayamas

Select * from
(
SELECT S.SectionID,convert(varchar, R.Date,1) as aDate, R.Sequence, R.Title
,Pages = SUM(PageCount),
Status = (SELECT Name FROM tblStatus WHERE StatusID =
(SELECT MIN(StatusID) FROM tblSectionStatus SS
WHERE SS.SectionID = S.SectionID))
FROM tblReports R
INNER JOIN tblS .... blah blah blah
)t where Status = null

where Status = null

should be

where Status is null

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-16 : 10:31:20

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-16 : 10:43:34

quote:
Originally posted by madhivanan

quote:
Originally posted by ayamas

Select * from
(
SELECT S.SectionID,convert(varchar, R.Date,1) as aDate, R.Sequence, R.Title
,Pages = SUM(PageCount),
Status = (SELECT Name FROM tblStatus WHERE StatusID =
(SELECT MIN(StatusID) FROM tblSectionStatus SS
WHERE SS.SectionID = S.SectionID))
FROM tblReports R
INNER JOIN tblS .... blah blah blah
)t where Status = null

where Status = null

should be

where Status is null

Madhivanan

Failing to plan is Planning to fail

Thanks for pointing it.

ann
Posting Yak Master

220 Posts

Posted - 2009-04-16 : 10:50:54

Select * from
(
SELECT S.SectionID,convert(varchar, R.Date,1) as aDate, R.Sequence, R.Title
,Pages = SUM(PageCount),
Status = (SELECT Name FROM tblStatus WHERE StatusID =
(SELECT MIN(StatusID) FROM tblSectionStatus SS
WHERE SS.SectionID = S.SectionID))
FROM tblReports R
INNER JOIN tblSections S ON S.ReportID = R.ReportID
INNER JOIN tblSectionPages SP ON SP.SectionID = S.SectionID
Group by S.SectionID, R.Date, Sequence, Title
)t where Status = null
GROUP BY SectionID, aDate, Sequence, Title
ORDER BY aDate DESC, Title, Sequence

I now have the following error:
Msg 8120, Level 16, State 1, Line 1
Column 't.Pages' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

I tried adding the t.Pages in the Group by then ended up with another error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t.Pages" could not be bound.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources