Hi All,

I'm trying to load the date data from a standard ingres table into a Vectorwise equivalent.

The standard ingres table uses ingresdate. The vectorwise equivalent uses timestamp(0).

Having created the VW table, I do:
insert into dtime_value select ...timestamp(value),... from std_dtime_value;

And get error: E_VW1027_REWRITER_ERROR

There are no more details displayed in the errlog.

What have I done wrong? The VW table has a primary key and an index defined.. But the timesatmp data is party of neither.
The ingresdate values are a mixture of pure date and date/time...but timestamp() doesn't seem to have a problem with this, certainly it has no problemwhen I do an equivaleny insert into a non VW table.

Martin Bowes

I believe that one of the current restrictions of the VW release is that
you can't use both standard-Ingres and Vectorwise tables in the same
statement.


--
Bodo

Hi Marty,

You will need to execute this in two stages - use an intermediate ingres
table that has the ingresdate column changed to timestamp. Otherwise you
will see the error you're getting due to the presence of an ingresdate
datatype being seen by the Vectorwise interface.

Regards,

Ian

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 11 August 2010 14:51
To: Ingres and related product discussion forum
Subject: [Info-Ingres] VW ingresdate --> timestamp conversion



Hi All,



I'm trying to load the date data from a standard ingres table into a
Vectorwise equivalent.



The standard ingres table uses ingresdate. The vectorwise equivalent
uses timestamp(0).



Having created the VW table, I do:

insert into dtime_value select ...timestamp(value),... from
std_dtime_value;



And get error: E_VW1027_REWRITER_ERROR



There are no more details displayed in the errlog.



What have I done wrong? The VW table has a primary key and an index
defined. But the timesatmp data is party of neither.

The ingresdate values are a mixture of pure date and date/time...but
timestamp() doesn't seem to have a problem with this, certainly it has
no problem when I do an equivaleny insert into a non VW table.



Martin Bowes

On Aug 11, 6:51*am, Martin Bowes <martin.bo... (AT) ctsu (DOT) ox.ac.uk> wrote:
As per other replies mixing tables types with the IngresDate type is
the issue. One thing you could try instead of a temp table would be to
use Ingres Star. I.e. register both tables in a Star database and then
issue the INSERT AS SELECT... in the Star db . I've not tried this
idea out but it could help mask some of this stuff from the VW
interface by buffering it with Star.

The temp table is probably easier/quicker to do short term but it may
help if you need to do a lot of this queries in this form.

Chris

The suggestion for using Ingres Star is probably not going to work as Star does not recognise "vectorwise" as a table type. (you could try hacking the Star catalogues - but ... )

So the only supported way for getting data from an Ingres table into a VectorWise table is to use copy.

In a future release it will be possible to insert into ... select from (ingres --> VectorWise) and create table ... as select (ingres to VectorWise).

John

________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com on behalf of Chris
Sent: Thu 12/08/2010 19:59
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] VW ingresdate --> timestamp conversion



On Aug 11, 6:51 am, Martin Bowes <martin.bo... (AT) ctsu (DOT) ox.ac.uk> wrote:
As per other replies mixing tables types with the IngresDate type is
the issue. One thing you could try instead of a temp table would be to
use Ingres Star. I.e. register both tables in a Star database and then
issue the INSERT AS SELECT... in the Star db . I've not tried this
idea out but it could help mask some of this stuff from the VW
interface by buffering it with Star.

The temp table is probably easier/quicker to do short term but it may
help if you need to do a lot of this queries in this form.

Chris
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres