Error when performing a case when task

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)

Error when performing a case when task

Author

Topic

washingtondcmiss
Starting Member

3 Posts

Posted - 2011-03-01 : 19:53:02

select
v2.Sales_Boundary_Level ,
v2.Territory ,
v2.Region ,
v2.Area ,
v2.Division ,
v2.ProductID ,
sum(case when v2.months = '2010, Month 1' then v2.value else 0 end) as Jan_Plan,
sum(case when v2.months = '2010, Month 2' then v2.value else 0 end) as Feb_Plan,
sum(case when v2.months = '2010, Month 3' then v2.value else 0 end) as Mar_Plan,
sum(case when v2.months = '2010, Month 4' then v2.value else 0 end) as Apr_Plan,
sum(case when v2.months = '2010, Month 5' then v2.value else 0 end) as May_Plan,
sum(case when v2.months = '2010, Month 6' then v2.value else 0 end) as Jun_Plan,
sum(case when v2.months = '2010, Month 7' then v2.value else 0 end) as Jul_Plan,
sum(case when v2.months = '2010, Month 8' then v2.value else 0 end) as Aug_Plan,
sum(case when v2.months = '2010, Month 9' then v2.value else 0 end) as Sep_Plan,
sum(case when v2.months = '2010, Month 10' then v2.value else 0 end) as Oct_Plan,
sum(case when v2.months = '2010, Month 11' then v2.value else 0 end) as Nov_Plan,
sum(case when v2.months = '2010, Month 12' then v2.value else 0 end) as Dec_Plan
from [QL Sales Plan by Chair Overall] v2
where v2.Months like '2010%'

I receive an error when I run this query. Any takes?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-01 : 20:23:18

What error did you get? I'm guessing it's something about can't can't appear in a SELECT list without being in the GROUP BY clause. See if this works:

select
v2.Sales_Boundary_Level ,
v2.Territory ,
v2.Region ,
v2.Area ,
v2.Division ,
v2.ProductID ,
sum(case when v2.months = '2010, Month 1' then v2.value else 0 end) as Jan_Plan,
sum(case when v2.months = '2010, Month 2' then v2.value else 0 end) as Feb_Plan,
sum(case when v2.months = '2010, Month 3' then v2.value else 0 end) as Mar_Plan,
sum(case when v2.months = '2010, Month 4' then v2.value else 0 end) as Apr_Plan,
sum(case when v2.months = '2010, Month 5' then v2.value else 0 end) as May_Plan,
sum(case when v2.months = '2010, Month 6' then v2.value else 0 end) as Jun_Plan,
sum(case when v2.months = '2010, Month 7' then v2.value else 0 end) as Jul_Plan,
sum(case when v2.months = '2010, Month 8' then v2.value else 0 end) as Aug_Plan,
sum(case when v2.months = '2010, Month 9' then v2.value else 0 end) as Sep_Plan,
sum(case when v2.months = '2010, Month 10' then v2.value else 0 end) as Oct_Plan,
sum(case when v2.months = '2010, Month 11' then v2.value else 0 end) as Nov_Plan,
sum(case when v2.months = '2010, Month 12' then v2.value else 0 end) as Dec_Plan
from [QL Sales Plan by Chair Overall] v2
where v2.Months like '2010%'
group by v2.Sales_Boundary_Level ,
v2.Territory ,
v2.Region ,
v2.Area ,
v2.Division ,
v2.ProductID

washingtondcmiss
Starting Member

3 Posts

Posted - 2011-03-01 : 20:47:49

The problem is with the column names. There is a prefix before the v2 on the column which is similar for all the columns on the view. When I add the prefix, I still get an error stating that I am using an invalid column name. I had the group by in the query already and it won't work

washingtondcmiss
Starting Member

3 Posts

Posted - 2011-03-01 : 20:52:21

This is the error message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Sales_Boundary_Level" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Territory" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Region" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Area" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Division" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.ProductID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Sales_Boundary_Level" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Territory" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Region" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Area" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.Division" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.ProductID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.months" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "v2.value" could not be bound.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-01 : 22:13:21

quote:
There is a prefix before the v2 on the column which is similar for all the columns on the view.

What do you mean "prefix"? Do you mean the column is named "v2.value"? If that's the case, then don't alias the view with v2, and put quotes "" around all the column references, or use square brackets [v2.value]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources