So Marty as John always says.
"Ingres is Open Source, just make the change for the Error.Message"
.
It's almost Friday again.
Kind regards
Henrik Georg Sørensen, Ingres Database Administrator, Denmark
Fra: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] På vegne af Martin Bowes
Sendt: 21. september 2011 17:11
Til: Ingres and related product discussion forum
Cc: John Smedley
Emne: Re: [Info-Ingres] Table procedure and E_OP0681
II 10.0.0 (a64.lnx/132)NPTL
14101
Marty
From: John Smedley [mailto:John.Smedley (AT) ingres (DOT) com]
Sent: 21 September 2011 15:45
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Table procedure and E_OP0681
Which version of Ingres (or VectorWise) are you using?
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 21 September 2011 15:35
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Table procedure and E_OP0681
Sorry ... my mistake...although I'm not so sure about the error produced.
It should have said E_XX9999 hey idiot, what the hell is correllation name 'c' when you havent defined it in a from clause?
Or words to that effect.
Marty
From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 21 September 2011 15:20
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Table procedure and E_OP0681
Hi All,
I have a table procedure 'countn' which I use as follows:
select p.packet_id, p.field_id, p.nrows, c.nrows
from pfield p, countn(field_id = p.field_id)
But this fails with an optimizer error: E_OP0681 consistency check
- unknown query tree node type found
Anyone have any idea whats causing this?
The QEP (which looks about right) is...
QUERY PLAN 1,1, timed out, of main query
Cart-Prod
Heap
Pages 89 Tups 12600
D7 C132
/ \
Proj-rest Proj-rest
Heap Heap
Pages 2 Tups 252 Pages 1 Tups 50
D1 C3 D6 C1
/ /
p countn
Heap Tproc(NU)
Pages 4 Tups 252 Pages 50 Tups 50
The procedure countn is defined as follows:
create procedure countn(
field_id integer4 not null
)
result row my_count(nrows integer4 not null)
as
declare
nrows integer4 not null not default;
value_type integer4 not null not default;
base_type integer4 not null not default;
begin
select value_type, base_type into :value_type, :base_type
from field
where field_id = :field_id;
if (base_type != 0) then
if (value_type = 21 and base_type = 11) then
if (field_id = 82 or field_id = 85 or field_id = 88 or field_id = 90) then
value_type = 41;
else
value_type = 11;
endif;
else
value_type = base_type;
endif;
endif;
if (value_type = 11) then
select count(*) into :nrows
from val_int
where field_id = :field_id;
elseif (value_type = 31) then
select count(*) into :nrows
from val_real
where field_id = :field_id;
elseif (value_type = 41) then
select count(*) into :nrows
from val_string
where field_id = :field_id;
elseif (value_type = 51) then
select count(*) into :nrows
from val_date
where field_id = :field_id;
elseif (value_type = 61) then
select count(*) into :nrows
from val_time
where field_id = :field_id;
else
nrows = -1;
endif;
return row (:nrows);
end;
Martin Bowes
[Info-Ingres] Table procedure and E_OP0681 -
09-21-2011
, 09:19 AM
Linear Mode
