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.
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 |
|
|
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? |
|
|
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 thisSELECT 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 |
|
|
forrestgump
Starting Member
19 Posts |
Posted - 2013-02-05 : 09:11:37
|
Thanks James K |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-06 : 07:48:35
|
You are very welcome Forrest - glad to help. |
|
|
|
|
|
|
|