dba123
asked on
The multi-part identifier "q.MonthName" could not be bound.
Why can't I reference Monthname using my p alias?
select * from
(
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
) as q
ORDER BY CASE q.MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, q.YearValue
select * from
(
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
) as q
ORDER BY CASE q.MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, q.YearValue
your Query seems to be OK
Do you have a column named monthname in the Dashboard_RevenueByMonth table?
ASKER
Yes, there is a column MonthName in that table.
did you try it on the QA or in EM
ASKER
Ok yes, that part is fine. I found that when it's added to my overall sp, then I get the error:
ALTER PROCEDURE [dbo].[SSRS_Get_RevenueByM onth]
@NumberOfMonthsBack int
AS
-- Now, generate the data for IL bars and insert into temp table
EXEC SSRS_Get_CurrentMonthColle ctions_AZ_ FeeBased
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL _FeeBased
UNION
-- Grab past month data
select * from
(
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
) as q
ORDER BY CASE q.MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, q.YearValue
ALTER PROCEDURE [dbo].[SSRS_Get_RevenueByM
@NumberOfMonthsBack int
AS
-- Now, generate the data for IL bars and insert into temp table
EXEC SSRS_Get_CurrentMonthColle
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL
UNION
-- Grab past month data
select * from
(
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
) as q
ORDER BY CASE q.MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, q.YearValue
SQL Server 2000 had problems with an alias in the ORDER BY clause. It is a bug that is fixed in 2005.
From this link: http://msdn2.microsoft.com/en-us/library/ms143359.aspx
Column names in the ORDER BY clause are resolved to columns listed in the select list, regardless if they are qualified.
For example, the following query executes without error:
USE pubs
SELECT au_fname AS 'FName',
au_lname AS 'LName'
FROM authors a
ORDER BY a.LName
SQL Server ignores the qualifier a in the ORDER BY clause and resolves the column name LName to the select list.
In 2005 this has been changed to:
Qualified column names and aliases are resolved to columns of tables listed in the FROM clause. If order_by_expression is not qualified, it must be unique among all columns listed in the SELECT statement.
For example, the following equivalent query returns an error:
USE AdventureWorks
SELECT FirstName AS 'FName',
LastName AS 'LName'
FROM Person.Contact p
ORDER BY p.LName
SQL Server does not ignore the qualifier p in the ORDER BY clause, and resolves the column name LName to tables listed in the FROM clause. But the FROM clause does not recognize that column LName is a column alias of table p.
Column names in the ORDER BY clause are resolved to columns listed in the select list, regardless if they are qualified.
For example, the following query executes without error:
USE pubs
SELECT au_fname AS 'FName',
au_lname AS 'LName'
FROM authors a
ORDER BY a.LName
SQL Server ignores the qualifier a in the ORDER BY clause and resolves the column name LName to the select list.
In 2005 this has been changed to:
Qualified column names and aliases are resolved to columns of tables listed in the FROM clause. If order_by_expression is not qualified, it must be unique among all columns listed in the SELECT statement.
For example, the following equivalent query returns an error:
USE AdventureWorks
SELECT FirstName AS 'FName',
LastName AS 'LName'
FROM Person.Contact p
ORDER BY p.LName
SQL Server does not ignore the qualifier p in the ORDER BY clause, and resolves the column name LName to tables listed in the FROM clause. But the FROM clause does not recognize that column LName is a column alias of table p.
why wont you put order by inside (no need for alia here)
=======================
EXEC SSRS_Get_CurrentMonthColle ctions_AZ_ FeeBased
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL _FeeBased
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
ORDER BY CASE MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, YearValue
=======================
EXEC SSRS_Get_CurrentMonthColle
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
ORDER BY CASE MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, YearValue
ASKER
>>>SQL Server does not ignore the qualifier p in the ORDER BY clause, and resolves the column name LName to tables listed in the FROM clause. But the FROM clause does not recognize that column LName is a column alias of table p.
Ok, I don't quite understand that...so what is the solution?
Ok, I don't quite understand that...so what is the solution?
ASKER
EXEC SSRS_Get_CurrentMonthColle ctions_AZ_ FeeBased
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL _FeeBased
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
ORDER BY CASE MonthName
-------------------------- ---------- ---------- ---------- ---------- -------
Msg 207, Level 16, State 1, Line 22
Invalid column name 'MonthName'.
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
ORDER BY CASE MonthName
--------------------------
Msg 207, Level 16, State 1, Line 22
Invalid column name 'MonthName'.
ASKER
whoops:
EXEC SSRS_Get_CurrentMonthColle ctions_AZ_ FeeBased
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL _FeeBased
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
ORDER BY CASE MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, YearValue
-------------------------- ---------- ---------- ---------- --
Invalid column name 'MonthName'.
EXEC SSRS_Get_CurrentMonthColle
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
ORDER BY CASE MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, YearValue
--------------------------
Invalid column name 'MonthName'.
ASKER
>>>If order_by_expression is not qualified, it must be unique among all columns listed in the SELECT statement.
So how do I qualify it?
So how do I qualify it?
ASKER
EXEC SSRS_Get_CurrentMonthColle ctions_AZ_ FeeBased
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL _FeeBased
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
ORDER BY CASE Dashboard_RevenueByMonth.d bo.MonthNa me
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, YearValue
I know for sure this field is in that table and spelled correctly in my SQL statement....so I don't have a clue why I'm getting this error for the above change:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Dashboard_RevenueByMonth. dbo.MonthN ame" could not be bound.
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
0 as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth
ORDER BY CASE Dashboard_RevenueByMonth.d
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, YearValue
I know for sure this field is in that table and spelled correctly in my SQL statement....so I don't have a clue why I'm getting this error for the above change:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Dashboard_RevenueByMonth.
ASKER
This works:
SELECT * FROM (
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL _FeeBased
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
'' as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth ) A
ORDER BY CASE MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, YearValue
SELECT * FROM (
SELECT *, DATENAME(mm, GetDate()) as MonthName, DATEPART(yy, GetDate()) as YearValue FROM CurrentMonthCollections_IL
UNION
SELECT ProjFee,
0 as ProjectGross,
0 as DailyRunRate,
0 as Var1,
0 as InHouse1,
InHouse AS InHouse2,
0 as GrossGoal,
'' as GroupName,
0 as PostedAmount,
Budget,
MonthName,
YearValue
FROM Dashboard_RevenueByMonth ) A
ORDER BY CASE MonthName
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
ELSE 99
END ASC, YearValue
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.