hi cassio,
there's a testcase QuerTest#testReportQueryAlias that uses aliases to choose columns. i added "NAMES.allArticlesInGroup.articleName" to have the same column twice.
Criteria crit1 = new Criteria();
crit1.setAlias("NAMES");
crit1.addLike("upper(allArticlesInGroup.articleName)", "F%");Criteria crit2 = new Criteria();
crit2.setAlias("STOCKS");
crit2.addGreaterOrEqualThan("allArticlesInGroup.stock", new Integer(110));crit1.addAndCriteria(crit2);
ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class, crit1);
q.setAttributes(new String[]{
"groupId","groupName",
"STOCKS.allArticlesInGroup.articleName",
"NAMES.allArticlesInGroup.articleName", "NAMES.allArticlesInGroup.stock"});Iterator iter = broker.getReportQueryIteratorByQuery(q);
the generate sql is as follows, and imo the aliases are correctly resolved
A2.Artikelname,A1.Artikelname,A1.Lagerbestand
SELECT A0.Kategorie_Nr,A0.KategorieName,A2.Artikelname,A1.Artikelname,A1.Lagerbestand FROM Kategorien A0 LEFT OUTER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr LEFT OUTER JOIN BOOKS A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr LEFT OUTER JOIN CDS A1E2 ON A0.Kategorie_Nr=A1E2.Kategorie_Nr LEFT OUTER JOIN Artikel A2 ON A0.Kategorie_Nr=A2.Kategorie_Nr LEFT OUTER JOIN BOOKS A2E1 ON A0.Kategorie_Nr=A2E1.Kategorie_Nr LEFT OUTER JOIN CDS A2E2 ON A0.Kategorie_Nr=A2E2.Kategorie_Nr WHERE ( (upper(A1.Artikelname) LIKE 'F%' OR upper(A1E1.Artikelname) LIKE 'F%' OR upper(A1E2.Artikelname) LIKE 'F%')) AND ((A2.Lagerbestand >= '110' OR A2E1.Lagerbestand >= '110' OR A2E2.Lagerbestand >= '110'))
jakob
J.Braeuchi schrieb:
Hi Jacob,
I apologize for sending this directly to you. I tried to send it to the ojb users list but it returned with an error. I tried your fix (files SqlQueryStatement.java and SqlSelectStatement.java, is there anything else I'm missing?).
I don't get the "undefined alias" error message anymore but I don't always
get the right query result. I'm giving some code extract and the generated
SQL query below. My report query extracts different attribute values using
different aliases but in the generated SQL, the values returned for the 2
different attribute extractions are exactly the same (A3.
reference_object_repository_id), and the join conditions don't look right to
me. I hope the code extract below will allow you to see where the problem
is, let me know otherwise.
If I change the path expressions used in the call to method setAttributes as
follows, then I get the right SQL query and result. Am I doing something
wrong?
q.setAttributes(new String[]{"repositoryID_", "className_",
"HASVIEW.references_.referenceObjectRepositoryID_","PROPERTIES.references_.r
eferenceObjectRepositoryID_"}); Thanks,
- Cassio
ArrayList list = new java.util.ArrayList();
Criteria crit1 = new Criteria(); crit1.setAlias("HASVIEW"); crit1.addEqualTo("references_.featureID_", new Integer(11));
Criteria crit2 = new Criteria(); crit2.setAlias("PROPERTIES"); crit2.addEqualTo("references_.featureID_", new Integer(17));
crit1.addAndCriteria(crit2);
ReportQueryByCriteria q =
QueryFactory.newReportQuery(PersistentEObject.class, crit1);
q.setAttributes(new String[]{"repositoryID_", "className_",
"HASVIEW.references_.referenceObjectRepositoryID_","PROPERTIES.references_.r
eferenceObjectRepositoryID_"});
GENERATED SQL:SELECT
A0.repository_id,A0.class_name,A3.reference_object_repository_id,A3.referenc
e_object_repository_id FROM EObject A0 INNER JOIN EReferenceValue A1 ON
A0.repository_id=A1.container_repository_id INNER JOIN EReferenceValue A2 ON
A0.repository_id=A2.container_repository_id INNER JOIN EReferenceValue A3 ON
A0.repository_id=A3.container_repository_id WHERE ( A1.feature_id = ?) AND
(A2.feature_id = ?)
--------------------------------------------------------------------- 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]
