db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: row value constructor syntax (tuple syntax)
Date Mon, 16 Jul 2007 09:21:02 GMT
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)

-- 
Knut Anders

Mime
View raw message