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]
