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
LVL 1
dba123Asked:
Who is Participating?
 
GranModConnect With a Mentor Commented:
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
your Query seems to be OK
0
 
ptjcbCommented:
Do you have a column named monthname in the Dashboard_RevenueByMonth table?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dba123Author Commented:
Yes, there is a column MonthName in that table.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
did you try it on the QA or in EM
0
 
dba123Author Commented:
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_RevenueByMonth]

@NumberOfMonthsBack     int

AS


-- Now, generate the data for IL bars and insert into temp table

EXEC SSRS_Get_CurrentMonthCollections_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
0
 
ptjcbCommented:
SQL Server 2000 had problems with an alias in the ORDER BY clause. It is a bug that is fixed in 2005.
0
 
ptjcbCommented:
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.
0
 
imran_fastCommented:
why wont you put order by inside (no need for alia here)
=======================


EXEC SSRS_Get_CurrentMonthCollections_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
 
0
 
dba123Author Commented:
>>>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?
0
 
dba123Author Commented:
EXEC SSRS_Get_CurrentMonthCollections_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'.
0
 
dba123Author Commented:
whoops:

EXEC SSRS_Get_CurrentMonthCollections_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'.
0
 
dba123Author Commented:
>>>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?
0
 
dba123Author Commented:
EXEC SSRS_Get_CurrentMonthCollections_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.dbo.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



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.MonthName" could not be bound.
0
 
dba123Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.