You are viewing a plain text version of this content. The canonical link for it is here.

Posted to derby-user@db.apache.org by Duncan Groenewald <da...@optusnet.com.au> on 2010/05/10 16:16:09 UTC

Subquery Error

The following query fails complaining about the subquery returning multiple records even though the select query works fine on its own.

insert into TableA (ID, ParentID, IndexNo, Type, Description, ProjectCode)
select A1.NR, 
(select A2.NR from TableB A2
where A2.WBSCODE = A1.PARENTID), 
IndexNo, Type, Title, 'AAA02'
from TABLEB A1


SELECT query runs fine on its own

select A1.NR, 
(select A2.NR from TableB A2
where A2.WBSCODE = A1.PARENTID), 
IndexNo, Type, Title, 'AAA02'
from TABLEB A1

Any suggestions ?

I have also tried using a VIEW which also works fine if its just a 'SELECT * from TABLEB'.  But I get the same error when trying a INSERT INTO TABLEA () SELECT * FROM TABLEB.

Thanks

Duncan

Re: Subquery Error

Posted by "Dag H. Wanvik" <Da...@Sun.COM>.

Duncan Groenewald <da...@optusnet.com.au> writes:

> The following query fails complaining about the subquery returning multiple records even though the select query works fine on its own.
>
> insert into TableA (ID, ParentID, IndexNo, Type, Description, ProjectCode)
> select A1.NR, 
> (select A2.NR from TableB A2
> where A2.WBSCODE = A1.PARENTID), 
> IndexNo, Type, Title, 'AAA02'
> from TABLEB A1

Sounds weird.. Could the subquery

     select A2.NR from TableB A2 where A2.WBSCODE = A1.PARENTID

ever return more than one row? If not, this sounds like a bug. Can
you show us the DDL and indicate any indexes you might have here?
Or ever better, make a self contained repro?

Dag


>
>
> SELECT query runs fine on its own
>
> select A1.NR, 
> (select A2.NR from TableB A2
> where A2.WBSCODE = A1.PARENTID), 
> IndexNo, Type, Title, 'AAA02'
> from TABLEB A1
>
> Any suggestions ?
>
> I have also tried using a VIEW which also works fine if its just a 'SELECT * from TABLEB'.  But I get the same error when trying a INSERT INTO TABLEA () SELECT * FROM TABLEB.
>
> Thanks
>
> Duncan

Re: Subquery Error

Posted by Duncan Groenewald <da...@optusnet.com.au>.

I just found the problem - there was a duplicate row and the select query was failing - I just needed to look far enough down the list to find the error.

Thanks anyway.


On 11/05/2010, at 12:16 AM, Duncan Groenewald wrote:

> The following query fails complaining about the subquery returning multiple records even though the select query works fine on its own.
> 
> insert into TableA (ID, ParentID, IndexNo, Type, Description, ProjectCode)
> select A1.NR, 
> (select A2.NR from TableB A2
> where A2.WBSCODE = A1.PARENTID), 
> IndexNo, Type, Title, 'AAA02'
> from TABLEB A1
> 
> 
> SELECT query runs fine on its own
> 
> select A1.NR, 
> (select A2.NR from TableB A2
> where A2.WBSCODE = A1.PARENTID), 
> IndexNo, Type, Title, 'AAA02'
> from TABLEB A1
> 
> Any suggestions ?
> 
> I have also tried using a VIEW which also works fine if its just a 'SELECT * from TABLEB'.  But I get the same error when trying a INSERT INTO TABLEA () SELECT * FROM TABLEB.
> 
> Thanks
> 
> Duncan
> 
> 
> 
> 

Duncan Groenewald
mobile: +61406291205
email: dagroenewald@optusnet.com.au