db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From metcox <met...@gmail.com>
Subject Re: row value constructor syntax (tuple syntax)
Date Mon, 16 Jul 2007 10:31:18 GMT
2007/7/16, Knut Anders Hatlen <Knut.Hatlen@sun.com>:
> metcox <metcox@gmail.com> 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
>

Mime
View raw message