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
 General SQL Server Forums
 New to SQL Server Programming
 Multi-part identifier error

Author  Topic 

forrestgump
Starting Member

19 Posts

Posted - 2013-02-05 : 07:44:21
I am trying to create the following calculation in a view (Q1_Dataset).

DATEDIFF(day, dbo.Data.Interview, dbo.Q1_Dataset.Offer_Made_Date) AS [No Of Days in Interview]

Unfortunately, I keep getting a 'Multi-part identifier error'. I am trying to use the [interview] date from table dbo.data and the calculated field in the same query [Offer_Made_Date] from (Q1_Dataset). Can anyone tell me what I am doing wrong?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 07:49:48
Assuming that you are referencing both the table and the view in the from clause, the only thing that I see is that you need to escape the table name (with square brackets as I shown below)
SELECT
DATEDIFF(day, dbo.[Data].Interview, dbo.Q1_Dataset.Offer_Made_Date) AS [No Of Days in Interview]
FROM
dbo.[Data]
INNER JOIN dbo.Q1_Dataset ON
-- add the join conditions here
Go to Top of Page

forrestgump
Starting Member

19 Posts

Posted - 2013-02-05 : 08:00:48
Hi James K,

Thanks for your reply. I am not currently referencing the table and the view in the from cause.

The view I have created takes data from the dbo.Data table and from 2 of the columns I created the 'Offer_Made_Date' e.g. CASE WHEN [UKUS] IS NOT NULL THEN [UKUS] ELSE [MoW] END AS Offer_Made_Date. Then I try and create the DATEDIFF calculation referencing the dbo.[Data].Interview column and the dbo.Q1_Dataset.Offer_Made_Date which I created. Do I need to create a join?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 08:08:12
If the Interview column is also part of the view, then you should refer to both columns from that view.
For example like this:
SELECT
DATEDIFF(day, dbo.Q1_Dataset.Interview, dbo.Q1_Dataset.Offer_Made_Date) AS [No Of Days in Interview]
FROM
dbo.Q1_Dataset

-- since you have only one view, there is no need to refer to the view, so it could even be this
SELECT
DATEDIFF(day, Interview, Offer_Made_Date) AS [No Of Days in Interview]
FROM
dbo.Q1_Dataset


-- but, it is a good practice to alias the table and explicitly refer to it like this:
SELECT
DATEDIFF(day, v1.Interview, v1.Offer_Made_Date) AS [No Of Days in Interview]
FROM
dbo.Q1_Dataset AS v1
Go to Top of Page

forrestgump
Starting Member

19 Posts

Posted - 2013-02-05 : 09:11:37
Thanks James K
Go to Top of Page

forrestgump
Starting Member

19 Posts

Posted - 2013-02-05 : 09:59:59
I can create the column in a separate view as described but not in the view I am creating (dbo.Q1_Dataset). I tried to just add the line:

DATEDIFF(day, dbo.Q1_Dataset.Interview, dbo.Q1_Dataset.Offer_Made_Date) AS [No Of Days in Interview]

but I still get the same error message. The first 3 DATEDIFF calculations below work, but not the fourth even though I have tried to reference them both to the view. I include the join I have created. Any ideas?

DATEDIFF(day, dbo.Data.[Met Minimum Requirements], dbo.Data.[Recruiter Approved]) AS [No Of Days in Met Min],
DATEDIFF(day, dbo.Data.[Recruiter Approved], dbo.Data.[Hiring Manager Approved]) AS [No Of Days in Rec Appro],
DATEDIFF(day, dbo.Data.[Hiring Manager Approved], dbo.Data.Interview) AS [No Of Days in HM Approved],
DATEDIFF(day, dbo.Q1_Dataset.Interview, dbo.Q1_Dataset.Offer_Made_Date) AS [No Of Days in Interview]

FROM dbo.Data LEFT OUTER JOIN dbo.HRStatusMap ON dbo.Data.[Current HR status] = dbo.HRStatusMap.TAS
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 10:11:27
This is because you don't have the view referred to in the FROM clause. The way SQL Server logically thinks about the query is starting with the FROM clause. It figures out what tables/views you will be using based on the tables listed there. In your example, you only have dbo.Data and dbo.HRStatusMap in the FROM clause. That means, it does not know anything about your view. So you would need to join on the view also.

Alternatively, you can simply use the formula for the Offer_Made_Date in your query:
DATEDIFF(day, dbo.Data.[Met Minimum Requirements], dbo.Data.[Recruiter Approved]) AS [No Of Days in Met Min], 
DATEDIFF(day, dbo.Data.[Recruiter Approved], dbo.Data.[Hiring Manager Approved]) AS [No Of Days in Rec Appro],
DATEDIFF(day, dbo.Data.[Hiring Manager Approved], dbo.Data.Interview) AS [No Of Days in HM Approved],
DATEDIFF(day, dbo.Data.Interview,
CASE WHEN dbo.Data.[UKUS] IS NOT NULL THEN dbo.Data.[UKUS] ELSEdbo.Data. [MoW] END
) AS [No Of Days in Interview]
FROM dbo.Data LEFT OUTER JOIN dbo.HRStatusMap ON dbo.Data.[Current HR status] = dbo.HRStatusMap.TAS
Regardless of whether you do that, or join on the view, the key thing to remember is that EVERY table or view that you refer to in the SELECT list must be in the FROM clause.
Go to Top of Page

forrestgump
Starting Member

19 Posts

Posted - 2013-02-06 : 05:56:07
Thanks once again James K it worked and thanks for the advice as well.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-06 : 07:48:35
You are very welcome Forrest - glad to help.
Go to Top of Page
   

- Advertisement -