October 23, 2014 at 9:17 am
Hi,
Can anyone tell me what's wrong with this code ...
I would like to have rows presented as columns. That's why I use the PIVOT function at the end.
The resultset will be presented in Excel using an external connection to the view.
When I try to save the view I get the error
Msg 4104, Level 16, State 1, Procedure _TEST, Line 47
The multi-part identifier "vk.OppCode" could not be bound.
Code (restricting the columns that I actually have to the relevant columns only):
USE [DBTest]
GO
/****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[_TEST]
AS
WITH Query1 AS
(
SELECTo.Code AS OppCode
FROMOpportunities o
),
Query2 AS
(
SELECTa.ProjectNumber AS ProjNo,
SUM(a.ItemCount) AS QtyHours,
SUM(a.AmountActual) AS AmtActual
FROMAbsences a
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (a.StartDate, a.EndDate)) t
WHEREa.Type = 3113
AND a.Status NOT IN (2, 5)
GROUP BY
a.ProjectNumber
),
Query3 AS
(
SELECTa.OpportunityID,
o.Code,
a.ProjectNumber,
a.Startdate,
MONTH(a.StartDate) AS MonthStart,
a.FreeNumberField_01 AS Amt
FROMAbsences a
LEFT OUTER JOIN Opportunities o ON a.OpportunityID = o.ID
WHEREa.Type = 3132
AND a.Status NOT IN (2, 5)
)
SELECTvk.OppCode,
[1] AS Januari,
[2] AS Februari,
[3] AS Maart,
[4] AS April,
[5] AS Mei,
[6] AS Juni,
[7] AS juli,
[8] AS Augustus,
[9] AS September,
[10] AS Oktober,
[11] AS November,
[12] AS December
FROMQuery1 vk
LEFT OUTER JOIN Query2 ur ON vk.OppCode = ur.ProjNo
LEFT OUTER JOIN Query3 mv ON vk.OppCode = mv.Code
PIVOT (SUM(mv.Amt) FOR mv.MonthStart IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS Months
GO
Thanks!
October 23, 2014 at 9:34 am
Your select list is actually pulling fields from the pivoted query, and you gave that query the alias "months", not vk.
Try "months.oppcode" or just "oppcode". Or to make it clearer, select the fields you want in another CTE (the query and the joins), then do the pivot last
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply