db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: Subquery Error
Date Mon, 10 May 2010 16:13:19 GMT
Duncan Groenewald <dagroenewald@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

Mime
View raw message