October 7, 2014 at 11:14 am
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "Fatals_CTE.t_enrollmentID" could not be bound.
Here are the table definitions:
CREATE TABLE [dbo].[Enroll](
[enrollmentID] [int] IDENTITY(10000,1) NOT NULL,
[e_PatientID] [int] NOT NULL,
[e_ProtocolNo] [varchar](30) NOT NULL,
[enrollDate] [datetime] NULL,
[enrollOK] [bit] NULL,
[LeaveDate] [datetime] NULL,
CONSTRAINT [PK_Enroll] PRIMARY KEY CLUSTERED ;
USE [SCRIDB]
GO
/****** Object: Table [dbo].[Toxicity] Script Date: 10/7/2014 12:06:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Toxicity](
[t_enrollmentID] [int] NOT NULL,
[Cycle] [tinyint] NOT NULL,
[Grade] [tinyint] NOT NULL,
[ToxicityID] [int] NOT NULL,
CONSTRAINT [PK_ToxicityFixed] PRIMARY KEY CLUSTERED
(
[t_enrollmentID] ASC,
[Cycle] ASC,
[Grade] ASC,
[ToxicityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Toxicity](
[t_enrollmentID] [int] NOT NULL,
[Cycle] [tinyint] NOT NULL,
[Grade] [tinyint] NOT NULL,
[ToxicityID] [int] NOT NULL,
CONSTRAINT [PK_ToxicityFixed] PRIMARY KEY CLUSTERED
(
[t_enrollmentID] ASC,
[Cycle] ASC,
[Grade] ASC,
[ToxicityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Toxicity] WITH CHECK ADD CONSTRAINT [Enroll_FK] FOREIGN KEY([t_enrollmentID])
REFERENCES [dbo].[Enroll] ([enrollmentID])
GO
Here's my attempt at a CTE...
WITH Fatals_CTE (t_enrollmentID, enrollDate, FirstFatalCycle)
AS
-- define the cte query
(SELECT t_enrollmentID, enrolldate, MIN(Cycle) AS FirstFatalCycle
FROM Toxicity t INNER JOIN Enroll e ON t.t_enrollmentID = e.enrollmentID
WHERE Grade=5
GROUP BY t_enrollmentID, enrollDate
)
-- define the outer query referencing the CTE
UPDATE enroll
SET LeaveDate = DATEADD(wk,2*Fatals_CTE.FirstFatalCycle,enrollDate)
WHERE enrollmentID = Fatals_CTE.t_enrollmentID;
What stupid thing am I overlooking? (I'm trying to update an enrollment's LeaveDate to the date of his Grade 5 event. The math is easy... the UPDATE statement is giving me fits!)
Thanks,
Pieter
October 7, 2014 at 12:24 pm
Elementary:blush:, add the aliases and a from clause
😎
UPDATE ER
SET ER.LeaveDate = DATEADD(wk,2, FC.enrollDate)
FROM Fatals_CTE FC
INNER JOIN DBO.Enroll ER
ON ER.enrollmentID = FC.t_enrollmentID;
October 7, 2014 at 12:31 pm
Thanks! I told you it was stupid!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply