Select returned multiple values.

[Database Administrator]

My colleague has just found something strange...
We have a procedure in the form

declare global temporary table session.temp as
select from tablea a, tableb b tablec c
where
and not exists (select * from tabled d
where d.column1 = a.column1
and d.column2 = c.column2)
on commit preserve rows with norecovery

tabled is a btree table whose primary unique key is (column1,column2)

He wanted to amend and, in order to test the changes
tried to run just the select part of the statement (not creating the
temporary table) in ISQL on a test database.

The query failed with error E_US1196 SELECT returned multiple values

reinstating the creation of the temporary table allows the SQL to
complete successfully

A quick glance at the QEP's from the two versions (with and without
the temporary table) suggests they are quite different, which seems
odd as the actual "select" is the same in both cases

Can anyone offer any insight as to why this might be...

[Database Administrator]

Hi TIm,

I don't know whether you have had any responses, but I have seen one
case of an E_US1196 error. It appears to have been resolved by a patch
containing the fix for bug 113519. This was back in March 2006, on
2.6. What version and platform are you running?

John


On Oct 29, 10:36 pm, Tim Ellis wrote:
> My colleague has just found something strange...
> We have a procedure in the form
>
> declare global temporary table session.temp as
> select from tablea a, tableb b tablec c
> where
> and not exists (select * from tabled d
> where d.column1 = a.column1
> and d.column2 = c.column2)
> on commit preserve rows with norecovery
>
> tabled is a btree table whose primary unique key is (column1,column2)
>
> He wanted to amend and, in order to test the changes
> tried to run just the select part of the statement (not creating the
> temporary table) in ISQL on a test database.
>
> The query failed with error E_US1196 SELECT returned multiple values
>
> reinstating the creation of the temporary table allows the SQL to
> complete successfully
>
> A quick glance at the QEP's from the two versions (with and without
> the temporary table) suggests they are quite different, which seems
> odd as the actual "select" is the same in both cases
>
> Can anyone offer any insight as to why this might be...

[Database Administrator]

On Nov 9, 10:34*am, John Dennis wrote:
> Hi TIm,
>
> I don't know whether you have had any responses, but I have seen one
> case of an E_US1196 error. It appears to have been resolved by a patch
> containing the fix for bug 113519. This was back in March 2006, on
> 2.6. *What version and platform are you running?

Thanks John
we are still currently on II2.0/0308 (axm.vms), though we should be
upgrading soon to the current release.