SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multi-part identifier error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

forrestgump
Starting Member

18 Posts

Posted - 02/05/2013 :  07:44:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 02/05/2013 :  07:49:48  Show Profile  Reply with Quote
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

18 Posts

Posted - 02/05/2013 :  08:00:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 02/05/2013 :  08:08:12  Show Profile  Reply with Quote
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

18 Posts

Posted - 02/05/2013 :  09:11:37  Show Profile  Reply with Quote
Thanks James K
Go to Top of Page

forrestgump
Starting Member

18 Posts

Posted - 02/05/2013 :  09:59:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 02/05/2013 :  10:11:27  Show Profile  Reply with Quote
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

18 Posts

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

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 02/06/2013 :  07:48:35  Show Profile  Reply with Quote
You are very welcome Forrest - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000