Inserting to a Partitioned View
-
I have created a partitioned view in a SQL Server 2005 Standard Edition database. Because it's standard edition, I cannot use the built-in partitioning features.
My attempts to insert new records to the partitions through the view are failing. This is the error message:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'clg.Labs' failed because it contains a derived or constant field.
I didn't intend to create a derived field or a constant field, but apparently I did. Can someone take a look at my view definition and tell me why I get this error? Thanks.
-- View Definition
CREATE VIEW [clg].[Labs]
WITH SCHEMABINDING
AS
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2000]
WHERE LabTestDate <= '12/31/2000 00:00:00:000'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2001]
WHERE LabTestDate BETWEEN '01/01/2001' AND '12/31/2001 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2002]
WHERE LabTestDate BETWEEN '01/01/2002' AND '12/31/2002 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2003]
WHERE LabTestDate BETWEEN '01/01/2003' AND '12/31/2003 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2004]
WHERE LabTestDate BETWEEN '01/01/2004' AND '12/31/2004 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2005]
WHERE LabTestDate BETWEEN '01/01/2005' AND '12/31/2005 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2006]
WHERE LabTestDate BETWEEN '01/01/2006' AND '12/31/2006 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2007]
WHERE LabTestDate BETWEEN '01/01/2007' AND '12/31/2007 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2008]
WHERE LabTestDate BETWEEN '01/01/2008' AND '12/31/2008 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2009]
WHERE LabTestDate BETWEEN '01/01/2009' AND '12/31/2009 23:59:59:999'
UNION ALL
SELECT [LabID], [NativeLabID], [AcctNumID], [NativeAcctNumID], [AcctTypeID], [NativeAcctTypeID], [PatientID],
[MRN], [CollectionDateTime], [LabtestID], [NativeLabtestID], [NativeLabResult], [ResultTypeID],
[NumericResultFormat], [CategoricResult], [bTrueFirst], [NormalSeverityID], [NativeNormalSeverityID],
[NormalRangeID], [NativeNormalRangeID], [ExternalOrderNumber], [Prefix], [RequestNumber], [Panel],
[NativeLabCommentID], [OrderIfNum], [NativeLabNote], [NativePatientID], [LabtestDate], [LabSource],
[NativeOrderingProviderId], [OrderingProviderId], [clgOrderingProviderId], [clinicId], [NativeclinicId],
[clgclinicId], [NumericLabResult], [UpdateJobID], [CreateJobID], [SourceDate]
FROM [clg].[Labs2010]
WHERE LabTestDate >= '01/01/2010 00:00:00:000';
WITH CHECK OPTION;
GO
-- Sample INSERT that fails
INSERT INTO clg.Labs (LabID, NativeLabID, NativeAcctNumID, AcctTypeID, NativeAcctTypeID, PatientID, MRN, CollectionDateTime, LabtestID, NativeLabtestID, NativeLabResult, ResultTypeID, NumericResultFormat, CategoricResult, bTrueFirst, NativeNormalSeverityID, NativeNormalRangeID, ExternalOrderNumber, Prefix, RequestNumber, NativeLabCommentID, NativeLabNote, NativePatientID, LabtestDate, LabSource, NumericLabResult, SourceDate, createjobid, updatejobid)
Values (123456789, '000000012345678', '12345678', 2, 'C', 1234567, '01234567', '01/16/2007 11:19:46', 123456, '1234', '**SEE NOTE', 1, 0, '**SEE NOTE', '0', '', '65', '07-123456', 7, 123456, '-999123456', 'Error in ordering by Lab', '123456', '01/16/2007 11:19:46', 'ABCDEF', 0.000000000, '01/16/2007', 100, 100);
“If you're not outraged at the media, you haven't been paying attention.”
-
Can you post the schema of one of the member tables?
-
Sure, here it is:
CREATE TABLE [clg].[Labs2007](
[LabID] [int] NOT NULL,
[NativeLabID] [varchar](24) NOT NULL,
[AcctNumID] [int] NULL,
[NativeAcctNumID] [varchar](12) NULL,
[AcctTypeID] [int] NULL,
[NativeAcctTypeID] [varchar](12) NULL,
[PatientID] [int] NOT NULL,
[MRN] [varchar](10) NOT NULL,
[CollectionDateTime] [datetime] NOT NULL,
[LabtestID] [int] NOT NULL,
[NativeLabtestID] [varchar](12) NOT NULL,
[NativeLabResult] [varchar](10) NOT NULL,
[ResultTypeID] [int] NOT NULL,
[NumericResultFormat] [smallint] NULL,
[CategoricResult] [varchar](30) NULL,
[bTrueFirst] [char](1) NULL,
[NormalSeverityID] [int] NULL,
[NativeNormalSeverityID] [varchar](12) NULL,
[NormalRangeID] [int] NULL,
[NativeNormalRangeID] [varchar](12) NULL,
[ExternalOrderNumber] [varchar](30) NULL,
[Prefix] [int] NULL,
[RequestNumber] [int] NULL,
[Panel] [varchar](3) NULL,
[NativeLabCommentID] [varchar](20) NULL,
[OrderIfNum] [int] NULL,
[NativeLabNote] [varchar](100) NULL,
[NativePatientID] [varchar](12) NULL,
[LabtestDate] [datetime] NOT NULL,
[LabSource] [varchar](12) NULL,
[NativeOrderingProviderId] [varchar](12) NULL,
[OrderingProviderId] [int] NULL,
[clgOrderingProviderId] [varchar](12) NULL,
[clinicId] [int] NULL,
[NativeclinicId] [varchar](12) NULL,
[clgclinicId] [varchar](12) NULL,
[NumericLabResult] [numeric](20, 9) NULL,
[UpdateJobID] [int] NOT NULL,
[CreateJobID] [int] NOT NULL,
[SourceDate] [datetime] NOT NULL,
CONSTRAINT [pkcLabs2007] PRIMARY KEY CLUSTERED
(
[LabID] ASC,
[LabtestDate] ASC
)WITH (PAD_INDEX = ON, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [INDEXES]
) ON [INDEXES]
ALTER TABLE [clg].[Labs2007] WITH CHECK ADD CONSTRAINT [chkLabTestDate2007] CHECK (([LabtestDate]>='01/01/2007' AND [LabtestDate]<='12/31/2007 23:59:59:999'))
“If you're not outraged at the media, you haven't been paying attention.”
-
All of the WHERE clauses in the view are unnecessary. The CHECK constraints on your tables take care of everything, provided SQL recognized the view as a partitioned view.
-Eddie
Eddie Wuerch
MCM: SQL -
Eddie is right, you don't need to put the where clauses in the view. They are defined in the CHECK constraint
-
All right, I'll give that a try. I re-read the Books Online article and I see now that they are not using WHERE clauses either. I'll rebuild the view without the WHERE clauses and try the inserts again.
Thanks to you and Eddie. I'll post again later today with my latest results.
“If you're not outraged at the media, you haven't been paying attention.”
-
I removed the WHERE clauses and tried the load again. The load fails with this message:
UNION ALL view 'CLGWarehouseSTAG.clg.Labs' is not updatable because a partitioning column was not found.
I also removed the "WITH CHECK OPTION" from the new view. Does that make a difference?
“If you're not outraged at the media, you haven't been paying attention.”
-
One of the things about partitioned view is that everything must be UNION compatible, what I mean is you can create a UNION ALL View with implict conversion of data types but to be partitioned view all the tables must be explictly UNION compatible. So check your table definition and make the correction as needed. Hope this helps.
Kind regards,
Gift Peddie -
I defined a federated server with dozens of distributed partitioned views before. I remember it was very easy to make mistakes when definining those partitioned views, like overlapping of check constraint. So I created script stored procedures to do the work.
Some times SQL server is not good to figure out it's a partitioning column or not. E.g. BOL always say partitioning columns when talking about partitioned view. But I found only one partitioned column was allowed and it must be the first column in the PK definition in a distributed partitioned view otherwise SQL sever couldn't always figure out the partitioning column.
For local partitioned view, it's not necessary that the partitioning column is the fisrt column of the PK.
You can try to start defining the view with two tables, test it and make sure it works. Then adding one more table.
-
I'll take a look and see if there are any compatibility issues with UNION. I noticed this one blurb in the "CREATE VIEW" article in Books Online:
"Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updateable."
So now I know why I'm getting the "computed column" error.
“If you're not outraged at the media, you haven't been paying attention.”
-
I'm big on using scripts myself, and you better believe I'll make some more scripts once I get this working!
“If you're not outraged at the media, you haven't been paying attention.”
-
The exception to that rule is the partitioned view but you columns must be pure UNION compatible, if you did not make sure of that SQL Server accepts your view but convert it to UNION ALL which is not updatable. These views are some of the most useful vendor creations but because they break SET algebra rules if it is not clean it becomes normal UNION ALL view which is not updatable. The quick place to clean it up is UNION data type requirements and avoid implict conversions. Hope this helps.
Kind regards,
Gift Peddie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply