[Info-ingres] E_OP0897 Consistency Check?

Tue May 15 13:42:12 UTC 2018

Ah, must have been something to do with that 'byref' statement I slipped in.

Cheers

Adrian

From: Adrian Williamson <adrian.williamson at rationalcommerce.com> 
Sent: 15 May 2018 14:40
To: 'Martin Bowes' <martin.bowes at ndph.ox.ac.uk>;
'info-ingres at lists.planetingres.org' <info-ingres at lists.planetingres.org>
Cc: 'Karl and Betty Schendel' <schendel at kbcomputer.com>
Subject: RE: [Info-ingres] E_OP0897 Consistency Check?

Hi,

My apologies Marty - you did correct it completely - I didn't try your full
solution I only did the inOUT procedure part.

Thinking now.

Cheers

Adrian

From: info-ingres-bounces at lists.planetingres.org
<mailto:info-ingres-bounces at lists.planetingres.org>
<info-ingres-bounces at lists.planetingres.org
<mailto:info-ingres-bounces at lists.planetingres.org> > On Behalf Of Martin
Bowes
Sent: 15 May 2018 14:33
To: Adrian Williamson <adrian.williamson at rationalcommerce.com
<mailto:adrian.williamson at rationalcommerce.com> >;
info-ingres at lists.planetingres.org
<mailto:info-ingres at lists.planetingres.org> 
Subject: Re: [Info-ingres] E_OP0897 Consistency Check?

Yes please,

Marty

From: Adrian Williamson [mailto:adrian.williamson at rationalcommerce.com] 
Sent: 15 May 2018 14:32
To: Martin Bowes; info-ingres at lists.planetingres.org
<mailto:info-ingres at lists.planetingres.org> 
Cc: 'Karl and Betty Schendel'
Subject: RE: [Info-ingres] E_OP0897 Consistency Check?

Hi,

Marty; I just tried your suggested syntax and it fails the same way as mine.

Karl; The columns all match for type (integer2 in this case):

Column Name              Type               Length Nulls Defaults Seq

dl_no                    char                    9   no      no     1

dlc_code                 char                    3   no      no     2

dly_prov_cnt             integer                 2   no      no

dlc_start_dt             ingresdate                  no      no

dlc_end_dt               ingresdate                  no      no

mto_code                 integer                 1   no      no

dlc_audit_us             char                   32   no     yes

dlc_audit_dt             ingresdate                  no     yes

opt_lock_cnt             integer                 1   no     yes

agne_code                integer                 2   no     yes

dlc_aud_agne             integer                 2   no     yes

This is the provoking statement:

insert into driving_lic_cat values ('16645875','A', 1, date('today'),
date('14-may-2020') , 0, 'DRIVERWS',date('now'),0,55,55);\g

There is a non-updateable view which makes reference to this table but other
than that it is uncomplicated.

Marty, I'll drop you over a create table statement if you want?

Karl, is there something I can switch on so I can try and narrow down where
it is getting upset?

Cheers

Adrian

From: info-ingres-bounces at lists.planetingres.org
<mailto:info-ingres-bounces at lists.planetingres.org>
<info-ingres-bounces at lists.planetingres.org
<mailto:info-ingres-bounces at lists.planetingres.org> > On Behalf Of Martin
Bowes
Sent: 15 May 2018 14:15
To: Adrian Williamson <adrian.williamson at rationalcommerce.com
<mailto:adrian.williamson at rationalcommerce.com> >;
info-ingres at lists.planetingres.org
<mailto:info-ingres at lists.planetingres.org> 
Subject: Re: [Info-ingres] E_OP0897 Consistency Check?

Hi Adrian,

My first guess would be something like.

create procedure incrementprvcnt(

    INOUT provcnt integer2 not null not default

)

as

begin

    provcnt=2;

end;

create rule proccntpatch

before insert into driving_lic_cat referencing new as newcat

where newcat.dlc_audit_us = 'DRIVERWS' and newcat.dly_prov_cnt = 1

for each row execute procedure incrementprvcnt(provcnt =
newcat.dly_prov_cnt)

If that doesn't work send me your table structure and I'll see what I can
do.

Marty

From: Adrian Williamson [ <mailto:adrian.williamson at rationalcommerce.com>
mailto:adrian.williamson at rationalcommerce.com] 
Sent: 15 May 2018 14:06
To:  <mailto:info-ingres at lists.planetingres.org>
info-ingres at lists.planetingres.org
Subject: [Info-ingres] E_OP0897 Consistency Check?

Hi,

My goto database ape (DBA) is off driving around Spain at the moment so I
was hoping someone could shed some light on how I managed to get this lovely
response after I created a rule fired procedure and then provoked it with an
insert statement:

E_OP0897 Consistency Check: a query tree constant or resdom node uses a

    source/destination type.

    (Tue May 15 13:54:25 2018)

I've not written too many rules in the past, and the trouble is it was in
the past. So I fully expect this to be user error somewhere.

This is syntax which I got the database to agree to:

create or replace procedure IncrementPrvCnt(provcnt = i2 not null) as begin
provcnt = 2 ;end;\g

create rule PrvCntPatch before insert into  driving_lic_cat referencing new
as newcat 

where newcat.dlc_audit_us = 'DRIVERWS' and newcat.dly_prov_cnt = 1

for each row execute procedure IncrementPrvCnt(provcnt =
byref(newcat.dly_prov_cnt));

What have I done wrong?

i.e. could someone translate the error message into something useful?

Cheers

Adrian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20180515/92d4637f/attachment.html>