Hi Jakob,
We are using Oralce 10g. It has problems with order by in sub queries.
Cheers,
Vasily
On 8/25/06, Jakob Braeuchi <[EMAIL PROTECTED]> wrote:
hi vasily,
forgot to post the sql and the testcase.
SELECT A0.Kategorie_Nr
FROM Kategorien A0
WHERE EXISTS (
SELECT B0.Artikel_Nr
FROM Artikel B0
WHERE (B0.Kategorie_Nr = A0.Kategorie_Nr) AND
B0.Artikelname LIKE '%Tofu%' ORDER BY 1)
this sql breaks in hsqldb.
public void testSubQueryExists()
{
ReportQueryByCriteria subQuery;
Criteria subCrit = new Criteria();
Criteria crit = new Criteria();
subCrit.addEqualToField("productGroupId",
Criteria.PARENT_QUERY_PREFIX + "groupId");
subCrit.addLike("articleName", "%Tofu%");
subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
subQuery.setAttributes(new String[]{"articleId"});
subQuery.addOrderByAscending("articleId"); // PROBLEMS in hsqldb
crit.addExists(subQuery);
Query q = QueryFactory.newQuery(ProductGroup.class, crit);
Collection results = broker.getCollectionByQuery(q);
assertNotNull(results);
assertEquals(1, results.size());
}
jakob
Jakob Braeuchi schrieb:
> hi vasily,
>
> order by in the subquery seems to be a problem for hsqldb. it works in
> mysql.
>
> jakob
>
> Vasily Ivanov schrieb:
>> Hi All,
>>
>> I've got the following code:
>> ===========Code===========
>> //build sub query
>> Criteria subCriteria = new Criteria();
>> subCriteria.addEqualToField("parentId", Criteria.PARENT_QUERY_PREFIX +
>> "id");
>> subCriteria.addIn("someChildFeild", someCollection);
>>
>> ReportQueryByCriteria subQuery =
>> QueryFactory.newReportQuery(Child.class, subCriteria);
>> subQuery.setAttributes(new String[] { "1" });
>> subQuery.addOrderByDescending("someChildFeild"); //******
>>
>> //build main query
>> Criteria mainCriteria = new Criteria();
>> mainCriteria.addExists(subQuery);
>>
>> ReportQueryByCriteria mainQuery =
>> QueryFactory.newReportQuery(Parent.class, mainCriteria);
>> mainQuery.setAttributes(new String[] { "id", "someParentFeild1",
>> "someParentFeild2" });
>> mainQuery.addOrderByDescending("someParentFeild2");
>>
>> ===========Generated SQL===========
>> SELECT A0.ID,A0.SOME_PARENT_FEILD1,A0.SOME_PARENT_FEILD2
>> FROM PARENT A0
>> WHERE EXISTS (SELECT 1, B0.SOME_CHILD_FEILD as ojb_col_2
>> FROM CHILD B0
>> WHERE (B0.PARENT_ID = A0.ID)
>> AND (B0.SOME_CHILD_FEILD IN (?, ?))
>> ORDER BY 2 DESC)
>> ORDER BY 3 DESC
>> =================================
>> This SQL throws "ORA-00907: missing right parenthesis".
>>
>> If we remove line marked with //****** we'll get:
>> ===========Generated SQL===========
>> SELECT A0.ID,A0.SOME_PARENT_FEILD1,A0.SOME_PARENT_FEILD2
>> FROM PARENT A0
>> WHERE EXISTS (SELECT 1
>> FROM CHILD B0
>> WHERE (B0.PARENT_ID = A0.ID)
>> AND (B0.SOME_CHILD_FEILD IN (?, ?)))
>> ORDER BY 3 DESC
>> =================================
>> ...which works fine.
>>
>> Question: Should addExists(subQuery) check that subQuery doesn't have
>> any orderby added or it's up to developer?
>>
>> Cheers,
>> Vasily
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
