Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Location
    Lisbon, Portugal
    Posts
    2

    Red face Unanswered: Updatable Partitioned Views

    Hi,

    I'm having a problem with partitioned views. When I try to do an update to a view I get the following error:

    Server: Msg 4436, Level 16, State 12, Line 1
    UNION ALL view 'STORE_PART_SALES' is not updatable because a partitioning column was not found.

    The partitioned view is:

    CREATE VIEW STORE_PART_SALES
    AS
    SELECT *
    FROM STORE_PART_SALES_1997
    UNION ALL
    SELECT *
    FROM STORE_PART_SALES_1998
    UNION ALL
    SELECT *
    FROM STORE_PART_SALES_1999
    UNION ALL
    SELECT *
    FROM STORE_PART_SALES_2000
    UNION ALL
    SELECT *
    FROM STORE_PART_SALES_2001
    UNION ALL
    SELECT *
    FROM STORE_PART_SALES_2002

    Each table is like this (example for the STORE_PART_SALES_2000 table):

    CREATE TABLE [SALES].[dbo].[STORE_PART_SALES_2000] (
    [STORE_ID] decimal (10,0) NOT NULL,
    [DAY_PART_ID] decimal (6,0) NOT NULL,
    [BUSINESS_DATE] datetime NOT NULL,
    [NET_SALES] decimal (15,2) NOT NULL,
    [GROSS_SALES] decimal (15,2) NULL,
    [NET_PRODUCT_SALES] decimal (15,2) NULL,
    [GROSS_PRODUCT_SALES] decimal (15,2) NULL,
    [TRANSCOUNT] decimal (6,0) NOT NULL,
    [CREW_HOURS] decimal (15,2) NULL,
    [CREW_LABOUR] decimal (15,2) NULL,
    [VALIDATED] varchar (1) NOT NULL,
    [SOURCE] varchar (1) NOT NULL,
    CONSTRAINT [SPS_2000_DPT_FK] FOREIGN KEY([DAY_PART_ID])
    REFERENCES [SALES].[dbo].[DAY_PART_TYPES]([DAY_PART_ID]),
    CONSTRAINT [SPS_2000_PK] PRIMARY KEY CLUSTERED(
    [STORE_ID], [DAY_PART_ID], [BUSINESS_DATE]),
    CONSTRAINT [SPS_2000_C001382] CHECK([VALIDATED] IN ( 'Y' , 'N' , 'W' )),
    CONSTRAINT [SPS_2000_C001383] CHECK([SOURCE] IN ( 'Y' , 'N' , 'W' ))),
    CONSTRAINT [SPS_2000_CK] CHECK(YEAR(BUSINESS_DATE) = 2000)

    The business_date column is the partitioning column.
    Can someone tell me what I'm doing wrong?!

    Thanks,
    Isabel

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •