You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "J.Braeuchi" <jb...@gmx.ch> on 2004/08/07 18:01:06 UTC
RE: regression with use of alias in ReportQuery setAttributes?
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: regression with use of alias in ReportQuery setAttributes?
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org