[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

The multi-part identifier "q.MonthName" could not be bound.

Posted on 2006-05-12
16
Medium Priority
?
1,031 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:dba123
  • 8
  • 3
  • 2
  • +2
15 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16670324
your Query seems to be OK
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16670545
Do you have a column named monthname in the Dashboard_RevenueByMonth table?
0
 
LVL 1

Author Comment

by:dba123
ID: 16670616
Yes, there is a column MonthName in that table.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16670641
did you try it on the QA or in EM
0
 
LVL 1

Author Comment

by:dba123
ID: 16670706
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 16672255
SQL Server 2000 had problems with an alias in the ORDER BY clause. It is a bug that is fixed in 2005.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16672268
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
 
LVL 28

Expert Comment

by:imran_fast
ID: 16682225
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
 
LVL 1

Author Comment

by:dba123
ID: 16689981
>>>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
 
LVL 1

Author Comment

by:dba123
ID: 16689999
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
 
LVL 1

Author Comment

by:dba123
ID: 16690009
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
 
LVL 1

Author Comment

by:dba123
ID: 16690021
>>>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
 
LVL 1

Author Comment

by:dba123
ID: 16690038
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
 
LVL 1

Author Comment

by:dba123
ID: 16691425
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
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16723495
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question