Select returned multiple values.

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

declare global temporary table session.temp as
select <some columns> from tablea a, tableb b tablec c
where <some conditions>
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 <some conditions> 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...

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 <some columns> from tablea a, tableb b tablec c
> where <some conditions>
> 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 <some conditions> 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...