Re: [Info-Ingres] Why are procedures fussy about tids?

[Database Administrator]

Hi Andrew,

Interestingly, using the table correllation name changed the error
message but not the result.
The message became: E_US0834 line 1, Table 'gtt' owned by '' does not
contain column 'tid'.

I'll raise this with IngresCorp as an error.

Marty

-----Original Message-----
From: info-ingres-bounces@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of
Andrew
Sent: 11 November 2008 16:00
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Why are procedures fussy about tids?

Try using a table alias, eg select my_tid = x.tid from mytable x


Martin Bowes wrote:
>
> It also fails on II2.6 and Ingres2006R2.
>
> I've attached a test script. Just run it against any database you
like.
>
> eg. sql iidbdb < test_this.sql
>
> Marty
>
> *From:* info-ingres-bounces@kettleriverconsulting.com
> [mailto:info-ingres-bounces@kettleriverconsulting.com] *On Behalf Of
> *Martin Bowes
> *Sent:* 11 November 2008 15:07
> *To:* info-ingres@kettleriverconsulting.com
> *Subject:* [Info-Ingres] Why are procedures fussy about tids?
>
> Hi All,
>
> I have a procedure which is fairly simple:
>
> create procedure show_x(gtt set of (id int4 not null))
>
> as
>
> declare
>
> my_tid int4 not null;
>
> id int4 not null;
>
> msg varchar(256) not null;
>
> begin
>
> SELECT FIRST 1 tid, id INTO :my_tid, :id FROM GTT ;
>
> msg = 'tid: ' + varchar(my_tid) + ', id: ' + varchar(id) ;
>
> MESSAGE msg ;
>
> end;
>
> However, when I attempt to load that procedure I get: E_US0836 line 1,

> Column 'tid' not found in any specified table.
>
> Now here's the funny thing...both the following work...
>
> 1. Rewrite the procedure to use tid rather than my_tid.
>
> create procedure show_x2(gtt set of (id int4 not null))
>
> as
>
> declare
>
> tid int4 not null;
>
> id int4 not null;
>
> msg varchar(256) not null;
>
> begin
>
> SELECT FIRST 1 tid, id INTO :tid, :id FROM GTT ;
>
> msg = 'tid2: ' + varchar(tid) + ', id: ' + varchar(id) ;
>
> MESSAGE msg ;
>
> end;
>
> 2. Rewrite the procedure to include an extra variable declaration for
> 'tid', but it is not used in the procedure.
>
> create procedure show_x3(gtt set of (id int4 not null))
>
> as
>
> declare
>
> my_tid int4 not null;
>
> tid int4 not null; /* Dummy declaration, it is not used */
>
> id int4 not null;
>
> msg varchar(256) not null;
>
> begin
>
> SELECT FIRST 1 tid, id INTO :my_tid, :id FROM GTT ;
>
> msg = 'tid: ' + varchar(my_tid) + ', id: ' + varchar(id) ;
>
> MESSAGE msg ;
>
> end;
>
> Anyone got an explanation for that behaviour?
>
> I'm using II 9.0.4 (a64.lnx/105)NPTL +patch12707
>
>
------------------------------------------------------------------------
>
> _______________________________________________
> Info-Ingres mailing list
> Info-Ingres@kettleriverconsulting.com
> http://www.kettleriverconsulting.com...fo/info-ingres
>

_______________________________________________
Info-Ingres mailing list
Info-Ingres@kettleriverconsulting.com
http://www.kettleriverconsulting.com...fo/info-ingres