2007/7/16, Knut Anders Hatlen <[EMAIL PROTECTED]>:
metcox <[EMAIL PROTECTED]> writes:
> Hi all,
>
> I've got trouble with the following request:
>
> from ResultAnnotation ra where
> ra.group.job.name='XPLE-B34' and ra.severity='FAILURE'
> and not (ra.target, ra.detailTarget) in (select rb.target,
> rb.detailTargetfrom ResultAnnotation rb where
> rb.group.job.name='XPLE-B33' and rb.severity='FAILURE')
>
>
> and I get this exception:
[...]
> Caused by: org.apache.derby.impl.jdbc.EmbedSQLException: Syntax error:
> Encountered "," at line 1, column 595.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
> at
>
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
> Source)
> ...
>
> What am I doing wrong ?
>
> It seems the problem comes from the use of tuple
> Does Derby support row value constructor syntax (tuple syntax)?
> If not, how can I rewrite my query to bypass this limitation?
You could perhaps use WHERE NOT EXISTS, like this:
SELECT * FROM ResultAnnotation ra WHERE
ra.group.job.name = 'XPLE-B34' AND ra.severity = 'FAILURE'
AND NOT EXISTS
(SELECT rb.target, rb.detailTarget FROM ResultAnnotation rb WHERE
rb.group.job.name = 'XPLE-B33' AND rb.severity = 'FAILURE'
AND rb.target = ra.target AND rb.detailTarget = ra.detailTarget)
I get the following exception:
java.sql.SQLException: Subquery is only allowed to return a single column.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
...
but I don't need to select target and detailTarget in the subquery. So
after removing the select in the subquery it works.
from ResultAnnotation ra where
ra.group.job.name = 'XPLE-B34' and ra.severity = 'FAILURE'
and not exists (from ResultAnnotation rb where
rb.group.job.name = 'XPLE-B33' and rb.severity = 'FAILURE'
and rb.target = ra.target and rb.detailTarget = ra.detailTarget)
thanks a lot.
Mathieu
--
Knut Anders
