[Info-ingres] E_OP0897 Consistency Check?

[Martin Bowes]

Oh good, because I was just typing in how that worked for me on my Ingres version etc.

Marty

From: Adrian Williamson [mailto:adrian.williamson at rationalcommerce.com]
Sent: 15 May 2018 14:40
To: Martin Bowes; info-ingres at lists.planetingres.org
Cc: 'Karl and Betty Schendel'
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]
Sent: 15 May 2018 14:06
To: info-ingres at lists.planetingres.org<mailto: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/e1860c1a/attachment.html>