Consulta sobre vistas particionadas

14/09/2005 - 23:20 por Jose Peñafiel T. | Informe spam

Alguién tiene alguna idea sobre este error??

Server: Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'plant_data' is not updatable because a partitioning column
was not found.

Esto me sucede al tratar de insertar sobre una vista particionada.

insert into PLANT_DATA

SELECT '09/16/2005',1,10004,500,0,0,0,0,0 FROM PDA09

Este es el scripts de las tablas

CREATE TABLE [dbo].[PDA09] (

[timestamp] [datetime] NOT NULL ,

[location_ptr] [decimal](5, 0) NOT NULL ,

[data_no] [decimal](5, 0) NOT NULL ,

[value] [decimal](18, 8) NOT NULL ,

[attribute_1] [decimal](5, 0) NOT NULL ,

[attribute_2] [decimal](5, 0) NOT NULL ,

[attribute_3] [decimal](5, 0) NOT NULL ,

[attribute_4] [decimal](5, 0) NOT NULL ,

[time_off] [datetime] NULL

) ON [PDA09]

GO

CREATE UNIQUE CLUSTERED INDEX [PDA09_Idx] ON [dbo].[PDA09]([timestamp],
[location_ptr], [data_no], [value], [attribute_1], [attribute_2],
[attribute_3], [attribute_4]) ON [PDA09]

GO

ALTER TABLE [dbo].[PDA09] ADD

CONSTRAINT [PK_PDA09_NEW] PRIMARY KEY NONCLUSTERED

(

[timestamp],

[location_ptr],

[data_no],

[value],

[attribute_1],

[attribute_2],

[attribute_3],

[attribute_4]

) ON [PDA09]

GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PDHIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[PDHIST]

GO

CREATE TABLE [dbo].[PDHIST] (

[TIMESTAMP] [datetime] NOT NULL ,

[LOCATION_PTR] [decimal](5, 0) NOT NULL ,

[DATA_NO] [decimal](5, 0) NOT NULL ,

[VALUE] [decimal](18, 8) NOT NULL ,

[ATTRIBUTE_1] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_2] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_3] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_4] [decimal](5, 0) NOT NULL ,

[TIME_OFF] [datetime] NULL

) ON [PDHIST]

GO

ALTER TABLE [dbo].[PDHIST] ADD

CONSTRAINT [PKey_PDHIST] PRIMARY KEY NONCLUSTERED

(

[TIMESTAMP],

[LOCATION_PTR],

[DATA_NO],

[VALUE],

[ATTRIBUTE_1],

[ATTRIBUTE_2],

[ATTRIBUTE_3],

[ATTRIBUTE_4]

) ON [PDHIST]

Siga el debate

5 respuestas

Tengo una respuesta

Preguntas similare

Una consulta sobre vistas previas

Leer las respuestas

#1 Maxi

15/09/2005 - 13:49 | Informe spam

Hola, el error es claro, la columna no existe en la vista!! revisa ello

Salu2
Maxi

"Jose Peñafiel T." escribió en el mensaje
news:

Alguién tiene alguna idea sobre este error??

Server: Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'plant_data' is not updatable because a partitioning column
was not found.

Esto me sucede al tratar de insertar sobre una vista particionada.

insert into PLANT_DATA

SELECT '09/16/2005',1,10004,500,0,0,0,0,0 FROM PDA09

Este es el scripts de las tablas

CREATE TABLE [dbo].[PDA09] (

[timestamp] [datetime] NOT NULL ,

[location_ptr] [decimal](5, 0) NOT NULL ,

[data_no] [decimal](5, 0) NOT NULL ,

[value] [decimal](18, 8) NOT NULL ,

[attribute_1] [decimal](5, 0) NOT NULL ,

[attribute_2] [decimal](5, 0) NOT NULL ,

[attribute_3] [decimal](5, 0) NOT NULL ,

[attribute_4] [decimal](5, 0) NOT NULL ,

[time_off] [datetime] NULL

) ON [PDA09]

GO

CREATE UNIQUE CLUSTERED INDEX [PDA09_Idx] ON [dbo].[PDA09]([timestamp],
[location_ptr], [data_no], [value], [attribute_1], [attribute_2],
[attribute_3], [attribute_4]) ON [PDA09]

GO

ALTER TABLE [dbo].[PDA09] ADD

CONSTRAINT [PK_PDA09_NEW] PRIMARY KEY NONCLUSTERED

(

[timestamp],

[location_ptr],

[data_no],

[value],

[attribute_1],

[attribute_2],

[attribute_3],

[attribute_4]

) ON [PDA09]

GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PDHIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[PDHIST]

GO

CREATE TABLE [dbo].[PDHIST] (

[TIMESTAMP] [datetime] NOT NULL ,

[LOCATION_PTR] [decimal](5, 0) NOT NULL ,

[DATA_NO] [decimal](5, 0) NOT NULL ,

[VALUE] [decimal](18, 8) NOT NULL ,

[ATTRIBUTE_1] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_2] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_3] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_4] [decimal](5, 0) NOT NULL ,

[TIME_OFF] [datetime] NULL

) ON [PDHIST]

GO

ALTER TABLE [dbo].[PDHIST] ADD

CONSTRAINT [PKey_PDHIST] PRIMARY KEY NONCLUSTERED

(

[TIMESTAMP],

[LOCATION_PTR],

[DATA_NO],

[VALUE],

[ATTRIBUTE_1],

[ATTRIBUTE_2],

[ATTRIBUTE_3],

[ATTRIBUTE_4]

) ON [PDHIST]

Responder a este mensaje

#2 Jose Peñafiel T.

15/09/2005 - 15:01 | Informe spam

Hola Maxi, gracias por responder.
A simple vista ese parecería el problema pero no es así, porque la vista que
te menciono esta compuesta por las 2 tablas que detallo en el script y
tienen exactamante la misma estructura.

CREATE VIEW dbo.plant_data as
SELECT * FROM PDA09
union all
SELECT * FROM PDHIST

Al hacer el insert sobre esta vista da el error, pero si le quito una de las
tablas me funciona, tienes idea de esto?? es posible hacer insert en una
vista de este tipo?? Tengo entendido que si...!!! donde puede estar el
error ???

"Maxi" wrote in message
news:

Hola, el error es claro, la columna no existe en la vista!! revisa ello

Salu2
Maxi

"Jose Peñafiel T." escribió en el mensaje
news:

Alguién tiene alguna idea sobre este error??

Server: Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'plant_data' is not updatable because a partitioning
column was not found.

Esto me sucede al tratar de insertar sobre una vista particionada.

insert into PLANT_DATA

SELECT '09/16/2005',1,10004,500,0,0,0,0,0 FROM PDA09

Este es el scripts de las tablas

CREATE TABLE [dbo].[PDA09] (

[timestamp] [datetime] NOT NULL ,

[location_ptr] [decimal](5, 0) NOT NULL ,

[data_no] [decimal](5, 0) NOT NULL ,

[value] [decimal](18, 8) NOT NULL ,

[attribute_1] [decimal](5, 0) NOT NULL ,

[attribute_2] [decimal](5, 0) NOT NULL ,

[attribute_3] [decimal](5, 0) NOT NULL ,

[attribute_4] [decimal](5, 0) NOT NULL ,

[time_off] [datetime] NULL

) ON [PDA09]

GO

CREATE UNIQUE CLUSTERED INDEX [PDA09_Idx] ON
[dbo].[PDA09]([timestamp], [location_ptr], [data_no], [value],
[attribute_1], [attribute_2], [attribute_3], [attribute_4]) ON [PDA09]

GO

ALTER TABLE [dbo].[PDA09] ADD

CONSTRAINT [PK_PDA09_NEW] PRIMARY KEY NONCLUSTERED

(

[timestamp],

[location_ptr],

[data_no],

[value],

[attribute_1],

[attribute_2],

[attribute_3],

[attribute_4]

) ON [PDA09]

GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PDHIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[PDHIST]

GO

CREATE TABLE [dbo].[PDHIST] (

[TIMESTAMP] [datetime] NOT NULL ,

[LOCATION_PTR] [decimal](5, 0) NOT NULL ,

[DATA_NO] [decimal](5, 0) NOT NULL ,

[VALUE] [decimal](18, 8) NOT NULL ,

[ATTRIBUTE_1] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_2] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_3] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_4] [decimal](5, 0) NOT NULL ,

[TIME_OFF] [datetime] NULL

) ON [PDHIST]

GO

ALTER TABLE [dbo].[PDHIST] ADD

CONSTRAINT [PKey_PDHIST] PRIMARY KEY NONCLUSTERED

(

[TIMESTAMP],

[LOCATION_PTR],

[DATA_NO],

[VALUE],

[ATTRIBUTE_1],

[ATTRIBUTE_2],

[ATTRIBUTE_3],

[ATTRIBUTE_4]

) ON [PDHIST]

Responder a este mensaje

#3 Alejandro Mesa

15/09/2005 - 15:05 | Informe spam

Jose,

Puedes postear el script de creacion de la vista?

AMB

"Jose Peñafiel T." wrote:

Alguién tiene alguna idea sobre este error??

Server: Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'plant_data' is not updatable because a partitioning column
was not found.

Esto me sucede al tratar de insertar sobre una vista particionada.

insert into PLANT_DATA

SELECT '09/16/2005',1,10004,500,0,0,0,0,0 FROM PDA09

Este es el scripts de las tablas

CREATE TABLE [dbo].[PDA09] (

[timestamp] [datetime] NOT NULL ,

[location_ptr] [decimal](5, 0) NOT NULL ,

[data_no] [decimal](5, 0) NOT NULL ,

[value] [decimal](18, 8) NOT NULL ,

[attribute_1] [decimal](5, 0) NOT NULL ,

[attribute_2] [decimal](5, 0) NOT NULL ,

[attribute_3] [decimal](5, 0) NOT NULL ,

[attribute_4] [decimal](5, 0) NOT NULL ,

[time_off] [datetime] NULL

) ON [PDA09]

GO

CREATE UNIQUE CLUSTERED INDEX [PDA09_Idx] ON [dbo].[PDA09]([timestamp],
[location_ptr], [data_no], [value], [attribute_1], [attribute_2],
[attribute_3], [attribute_4]) ON [PDA09]

GO

ALTER TABLE [dbo].[PDA09] ADD

CONSTRAINT [PK_PDA09_NEW] PRIMARY KEY NONCLUSTERED

(

[timestamp],

[location_ptr],

[data_no],

[value],

[attribute_1],

[attribute_2],

[attribute_3],

[attribute_4]

) ON [PDA09]

GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PDHIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[PDHIST]

GO

CREATE TABLE [dbo].[PDHIST] (

[TIMESTAMP] [datetime] NOT NULL ,

[LOCATION_PTR] [decimal](5, 0) NOT NULL ,

[DATA_NO] [decimal](5, 0) NOT NULL ,

[VALUE] [decimal](18, 8) NOT NULL ,

[ATTRIBUTE_1] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_2] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_3] [decimal](5, 0) NOT NULL ,

[ATTRIBUTE_4] [decimal](5, 0) NOT NULL ,

[TIME_OFF] [datetime] NULL

) ON [PDHIST]

GO

ALTER TABLE [dbo].[PDHIST] ADD

CONSTRAINT [PKey_PDHIST] PRIMARY KEY NONCLUSTERED

(

[TIMESTAMP],

[LOCATION_PTR],

[DATA_NO],

[VALUE],

[ATTRIBUTE_1],

[ATTRIBUTE_2],

[ATTRIBUTE_3],

[ATTRIBUTE_4]

) ON [PDHIST]

Responder a este mensaje

#4 Alejandro Mesa

15/09/2005 - 16:31 | Informe spam

Jose,

es posible hacer insert en una vista de este tipo??

Supongamos por un momento que tu eres sql server (no es mi intencion la
ofensa). Cuando haces:

insert into PLANT_DATA
SELECT '09/16/2005',1,10004,500,0,0,0,0,0
FROM PDA09

En cual de las dos tablas usadas por la vista, pondrias la data?

No sabes verdad?, pues eso mismo le pasa a sql server. Poara eso necesitas
una columna de particion (partitioning column) la cual se usa para
identificar en cual tabla debe ir cual data.

AMB

"Jose Peñafiel T." wrote:

Hola Maxi, gracias por responder.
A simple vista ese parecería el problema pero no es así, porque la vista que
te menciono esta compuesta por las 2 tablas que detallo en el script y
tienen exactamante la misma estructura.

CREATE VIEW dbo.plant_data as
SELECT * FROM PDA09
union all
SELECT * FROM PDHIST

Al hacer el insert sobre esta vista da el error, pero si le quito una de las
tablas me funciona, tienes idea de esto?? es posible hacer insert en una
vista de este tipo?? Tengo entendido que si...!!! donde puede estar el
error ???

"Maxi" wrote in message
news:
> Hola, el error es claro, la columna no existe en la vista!! revisa ello
>
>
> Salu2
> Maxi
>
>
> "Jose Peñafiel T." escribió en el mensaje
> news:
>> Alguién tiene alguna idea sobre este error??
>>
>>
>>
>> Server: Msg 4436, Level 16, State 12, Line 1
>>
>> UNION ALL view 'plant_data' is not updatable because a partitioning
>> column was not found.
>>
>>
>>
>>
>>
>> Esto me sucede al tratar de insertar sobre una vista particionada.
>>
>>
>>
>> insert into PLANT_DATA
>>
>> SELECT '09/16/2005',1,10004,500,0,0,0,0,0 FROM PDA09
>>
>>
>>
>>
>>
>>
>>
>> Este es el scripts de las tablas
>>
>>
>>
>> CREATE TABLE [dbo].[PDA09] (
>>
>> [timestamp] [datetime] NOT NULL ,
>>
>> [location_ptr] [decimal](5, 0) NOT NULL ,
>>
>> [data_no] [decimal](5, 0) NOT NULL ,
>>
>> [value] [decimal](18, 8) NOT NULL ,
>>
>> [attribute_1] [decimal](5, 0) NOT NULL ,
>>
>> [attribute_2] [decimal](5, 0) NOT NULL ,
>>
>> [attribute_3] [decimal](5, 0) NOT NULL ,
>>
>> [attribute_4] [decimal](5, 0) NOT NULL ,
>>
>> [time_off] [datetime] NULL
>>
>> ) ON [PDA09]
>>
>> GO
>>
>>
>>
>> CREATE UNIQUE CLUSTERED INDEX [PDA09_Idx] ON
>> [dbo].[PDA09]([timestamp], [location_ptr], [data_no], [value],
>> [attribute_1], [attribute_2], [attribute_3], [attribute_4]) ON [PDA09]
>>
>> GO
>>
>>
>>
>> ALTER TABLE [dbo].[PDA09] ADD
>>
>> CONSTRAINT [PK_PDA09_NEW] PRIMARY KEY NONCLUSTERED
>>
>> (
>>
>> [timestamp],
>>
>> [location_ptr],
>>
>> [data_no],
>>
>> [value],
>>
>> [attribute_1],
>>
>> [attribute_2],
>>
>> [attribute_3],
>>
>> [attribute_4]
>>
>> ) ON [PDA09]
>>
>> GO
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> if exists (select * from dbo.sysobjects where id =
>> object_id(N'[dbo].[PDHIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>>
>> drop table [dbo].[PDHIST]
>>
>> GO
>>
>>
>>
>> CREATE TABLE [dbo].[PDHIST] (
>>
>> [TIMESTAMP] [datetime] NOT NULL ,
>>
>> [LOCATION_PTR] [decimal](5, 0) NOT NULL ,
>>
>> [DATA_NO] [decimal](5, 0) NOT NULL ,
>>
>> [VALUE] [decimal](18, 8) NOT NULL ,
>>
>> [ATTRIBUTE_1] [decimal](5, 0) NOT NULL ,
>>
>> [ATTRIBUTE_2] [decimal](5, 0) NOT NULL ,
>>
>> [ATTRIBUTE_3] [decimal](5, 0) NOT NULL ,
>>
>> [ATTRIBUTE_4] [decimal](5, 0) NOT NULL ,
>>
>> [TIME_OFF] [datetime] NULL
>>
>> ) ON [PDHIST]
>>
>> GO
>>
>>
>>
>> ALTER TABLE [dbo].[PDHIST] ADD
>>
>> CONSTRAINT [PKey_PDHIST] PRIMARY KEY NONCLUSTERED
>>
>> (
>>
>> [TIMESTAMP],
>>
>> [LOCATION_PTR],
>>
>> [DATA_NO],
>>
>> [VALUE],
>>
>> [ATTRIBUTE_1],
>>
>> [ATTRIBUTE_2],
>>
>> [ATTRIBUTE_3],
>>
>> [ATTRIBUTE_4]
>>
>> ) ON [PDHIST]
>>
>>
>
>

Responder a este mensaje

#5 Jose Peñafiel T.

15/09/2005 - 21:45 | Informe spam

Excelente, ese era el problema, ya definí la columna de partición y he
solucionado el problema.
Gracias a todos.

"Alejandro Mesa" wrote in message
news:

Jose,

es posible hacer insert en una vista de este tipo??

Supongamos por un momento que tu eres sql server (no es mi intencion la
ofensa). Cuando haces:

insert into PLANT_DATA
SELECT '09/16/2005',1,10004,500,0,0,0,0,0
FROM PDA09

En cual de las dos tablas usadas por la vista, pondrias la data?

No sabes verdad?, pues eso mismo le pasa a sql server. Poara eso necesitas
una columna de particion (partitioning column) la cual se usa para
identificar en cual tabla debe ir cual data.

AMB

"Jose Peñafiel T." wrote:

Hola Maxi, gracias por responder.
A simple vista ese parecería el problema pero no es así, porque la vista
que
te menciono esta compuesta por las 2 tablas que detallo en el script y
tienen exactamante la misma estructura.

CREATE VIEW dbo.plant_data as
SELECT * FROM PDA09
union all
SELECT * FROM PDHIST

Al hacer el insert sobre esta vista da el error, pero si le quito una de
las
tablas me funciona, tienes idea de esto?? es posible hacer insert en una
vista de este tipo?? Tengo entendido que si...!!! donde puede estar el
error ???

"Maxi" wrote in message
news:
> Hola, el error es claro, la columna no existe en la vista!! revisa ello
>
>
> Salu2
> Maxi
>
>
> "Jose Peñafiel T." escribió en el mensaje
> news:
>> Alguién tiene alguna idea sobre este error??
>>
>>
>>
>> Server: Msg 4436, Level 16, State 12, Line 1
>>
>> UNION ALL view 'plant_data' is not updatable because a partitioning
>> column was not found.
>>
>>
>>
>>
>>
>> Esto me sucede al tratar de insertar sobre una vista particionada.
>>
>>
>>
>> insert into PLANT_DATA
>>
>> SELECT '09/16/2005',1,10004,500,0,0,0,0,0 FROM PDA09
>>
>>
>>
>>
>>
>>
>>
>> Este es el scripts de las tablas
>>
>>
>>
>> CREATE TABLE [dbo].[PDA09] (
>>
>> [timestamp] [datetime] NOT NULL ,
>>
>> [location_ptr] [decimal](5, 0) NOT NULL ,
>>
>> [data_no] [decimal](5, 0) NOT NULL ,
>>
>> [value] [decimal](18, 8) NOT NULL ,
>>
>> [attribute_1] [decimal](5, 0) NOT NULL ,
>>
>> [attribute_2] [decimal](5, 0) NOT NULL ,
>>
>> [attribute_3] [decimal](5, 0) NOT NULL ,
>>
>> [attribute_4] [decimal](5, 0) NOT NULL ,
>>
>> [time_off] [datetime] NULL
>>
>> ) ON [PDA09]
>>
>> GO
>>
>>
>>
>> CREATE UNIQUE CLUSTERED INDEX [PDA09_Idx] ON
>> [dbo].[PDA09]([timestamp], [location_ptr], [data_no], [value],
>> [attribute_1], [attribute_2], [attribute_3], [attribute_4]) ON [PDA09]
>>
>> GO
>>
>>
>>
>> ALTER TABLE [dbo].[PDA09] ADD
>>
>> CONSTRAINT [PK_PDA09_NEW] PRIMARY KEY NONCLUSTERED
>>
>> (
>>
>> [timestamp],
>>
>> [location_ptr],
>>
>> [data_no],
>>
>> [value],
>>
>> [attribute_1],
>>
>> [attribute_2],
>>
>> [attribute_3],
>>
>> [attribute_4]
>>
>> ) ON [PDA09]
>>
>> GO
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> if exists (select * from dbo.sysobjects where id >> >> object_id(N'[dbo].[PDHIST]') and OBJECTPROPERTY(id, N'IsUserTable') =
>> 1)
>>
>> drop table [dbo].[PDHIST]
>>
>> GO
>>
>>
>>
>> CREATE TABLE [dbo].[PDHIST] (
>>
>> [TIMESTAMP] [datetime] NOT NULL ,
>>
>> [LOCATION_PTR] [decimal](5, 0) NOT NULL ,
>>
>> [DATA_NO] [decimal](5, 0) NOT NULL ,
>>
>> [VALUE] [decimal](18, 8) NOT NULL ,
>>
>> [ATTRIBUTE_1] [decimal](5, 0) NOT NULL ,
>>
>> [ATTRIBUTE_2] [decimal](5, 0) NOT NULL ,
>>
>> [ATTRIBUTE_3] [decimal](5, 0) NOT NULL ,
>>
>> [ATTRIBUTE_4] [decimal](5, 0) NOT NULL ,
>>
>> [TIME_OFF] [datetime] NULL
>>
>> ) ON [PDHIST]
>>
>> GO
>>
>>
>>
>> ALTER TABLE [dbo].[PDHIST] ADD
>>
>> CONSTRAINT [PKey_PDHIST] PRIMARY KEY NONCLUSTERED
>>
>> (
>>
>> [TIMESTAMP],
>>
>> [LOCATION_PTR],
>>
>> [DATA_NO],
>>
>> [VALUE],
>>
>> [ATTRIBUTE_1],
>>
>> [ATTRIBUTE_2],
>>
>> [ATTRIBUTE_3],
>>
>> [ATTRIBUTE_4]
>>
>> ) ON [PDHIST]
>>
>>
>
>

Siga el debate Respuesta

Responder a este mensaje

Ads by Google

Hacer una pregunta

Tengo una respuesta

Busqueda sugerida