not exists criteria - does it work?

[Bonnie MacKellar <BM...@mobius.com>]

Is it true that you can't link the subquery in a not exists clause
(Criteria.addNotExists)
to the results in the outer query? What would be the point of using a not
exists clause
if you can't do that?

I have a table SuggestedPayments, which participates in a M to N
relationship
called CompletedBy, with a table called PaymentTxn.

I need to retrieve all SuggestedPayments that have no payment transactions
with a paid status.

The only way I can think to do this is with a not exists clause, retrieving
the payment transactions for a suggested payment in the subquery.
But then I need to link the subquery.

Is there another way?

thanks,
Bonnie MacKellar

Re: not exists criteria - does it work?

[Jakob Braeuchi <jb...@gmx.ch>]

hi bonnie,

this is possible but not yet documented because the prefix needs to be 
defined.
see QueryTest#testSubQuery4

    public void testSubQuery4()
    {

        ReportQueryByCriteria subQuery;
        Criteria subCrit = new Criteria();
        Criteria crit = new Criteria();

        
subCrit.addEqualToField("productGroupId",Criteria.PARENT_QUERY_PREFIX + 
"groupId");
        subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
        subQuery.setColumns(new String[]{"count(productGroupId)"});

        crit.addGreaterThan(subQuery,"10");    // MORE than 10 articles
        crit.addLessThan("groupId",new Integer(987654));
        Query q = QueryFactory.newQuery(ProductGroup.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() == 4);
    }

hth
jakob


Bonnie MacKellar wrote:

>Is it true that you can't link the subquery in a not exists clause
>(Criteria.addNotExists)
>to the results in the outer query? What would be the point of using a not
>exists clause
>if you can't do that?
>
>I have a table SuggestedPayments, which participates in a M to N
>relationship
>called CompletedBy, with a table called PaymentTxn.
>
>I need to retrieve all SuggestedPayments that have no payment transactions
>with a paid status.
>
>The only way I can think to do this is with a not exists clause, retrieving
>the payment transactions for a suggested payment in the subquery.
>But then I need to link the subquery.
>
>Is there another way?
>
>thanks,
>Bonnie MacKellar
>
>  
>


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org