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 Chandan AHUJA <ch...@st.com> on 2005/04/01 06:18:07 UTC
RE: Help Needed for Report Query Problem .
Hi Jakob ,
Providing an alias to the column doesnot help either .
When I try to provide an alias to the column "name" then the query
that is generated , does not qualify the column name ("name" in this case )
with the name of the table .
Since column "name" is in both tables "employee" as well as "project" ,
hence this query results in ambiguous column name error from the Database .
In the earlier case the query that was generated was correct but the result
that is displyaed on iterating through the iterator gave the wrong info .
May be the two "name" columns are confusing the "ReportQueryByCriteria" ???
Please advice some workaround for this .
Thanks and Best Regards ,
Chandan
-----Original Message-----
From: Jakob Braeuchi [mailto:jbraeuchi@gmx.ch]
Sent: Thursday, March 31, 2005 11:51 PM
To: OJB Users List
Subject: Re: Help Needed for Report Query Problem .
hi chandan,
this looks like a conflict in column names.
have you tried using an alias for project.name:
mainQuery.setAttributes(new String[] { "id" , "name" , "age" ,
"project.name as pname" });
hth
jakob
Chandan AHUJA schrieb:
> Hello All ,
> I am facing one problem while using ReportQueryByCriteria
> class .
>
> Backdrop : I have 2 tables
> A) Employee(Id , name , age ,project__id) ;
> B) project(id , name)
>
> Here , "Project__id" field of the "employee" table is the foreign key
> referencing the "id" field of "project" table . Following are the
> corresponding classes for the two tables
>
>
> //Only attributes are shown here
> public class Employee {
>
> private String id ;
> private String name;
> private Integer age ;
> private Project project ;
> private String projectId ;
> }
>
> public class Project
> {
>
> private String id ;
> private String name ;
> public Vector allEmployeesInProject ;
>
> }
>
> I have defined the relationship in repository_user.xml files for both
> the classes .
>
> I want to make the following query ......
>
> Select e.id , e.name , e.age , p.name from employee e, project p
> where e.project__id= p.id and
> e.age > ( select avg(age) from employee )
>
> I am using the following code for the same .
> ********************************************
> subQuery = new ReportQueryByCriteria(Employee.class, subCriteria);
> criteria.addGreaterThan("age" , subQuery) ;
> mainQuery = new ReportQueryByCriteria(Employee.class, criteria);
> mainQuery.setAttributes(new String[] { "id" , "name" , "age" ,
"project.name" });
> subQuery.setAttributes(new String[] { "avg(age)" });
> iterator = broker.getReportQueryIteratorByQuery(mainQuery);
> System.err.println("Id " + "||" + "Name " + "Age " + "||" + "
Project Name ") ;
> while(iterator.hasNext())
> {
> Object result[] = (Object[])(iterator.next());
> System.err.println( (String)result[0] + "||" + (String)result[1] +
"||"+ ((BigDecimal)result[2]) + "||" + (String)result[3] ) ;
> }
> *********************************************************
>
> I the log file spy.log I can see that the query generaed is exactly
> that I want .And it is producing the correct results when I run it on my
data base
> But , I am facing a strange problem in the "column 4th"(project.name) when
I try to print the result in stderr .
> The 4th element ie the result[4] is showing the Name of the "employee"
and not the name of the "project" .
> When I copy the query from the log file and run it in its running the
correct result ie the last column is the the project.name .
> Also , When I select "project.id" instead of "project.name" . I am getting
the id of the projects , ie the correct results .
>
> Please Help me .
> Thanks in advance .
>
> Chandan Ahuja
>
>
>
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Help Needed for Report Query Problem .
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi chandan,
aliasing the attribute name does not help. i was digging a little deeper
and i found that ReportQueryRsIterator retrieves the values by _index_
from the resultset, so the column name is imo not relevant.
could you please post the sql generated by ojb and also the version of ojb ?
jakob
Chandan AHUJA schrieb:
> Hi Jakob ,
> Providing an alias to the column doesnot help either .
> When I try to provide an alias to the column "name" then the query
> that is generated , does not qualify the column name ("name" in this case )
> with the name of the table .
> Since column "name" is in both tables "employee" as well as "project" ,
> hence this query results in ambiguous column name error from the Database .
>
> In the earlier case the query that was generated was correct but the result
> that is displyaed on iterating through the iterator gave the wrong info .
>
> May be the two "name" columns are confusing the "ReportQueryByCriteria" ???
>
> Please advice some workaround for this .
>
> Thanks and Best Regards ,
>
> Chandan
>
>
>
>
>
>
>
> -----Original Message-----
> From: Jakob Braeuchi [mailto:jbraeuchi@gmx.ch]
> Sent: Thursday, March 31, 2005 11:51 PM
> To: OJB Users List
> Subject: Re: Help Needed for Report Query Problem .
>
>
> hi chandan,
>
> this looks like a conflict in column names.
> have you tried using an alias for project.name:
>
> mainQuery.setAttributes(new String[] { "id" , "name" , "age" ,
> "project.name as pname" });
>
> hth
> jakob
>
> Chandan AHUJA schrieb:
>
>>Hello All ,
>> I am facing one problem while using ReportQueryByCriteria
>>class .
>>
>>Backdrop : I have 2 tables
>>A) Employee(Id , name , age ,project__id) ;
>>B) project(id , name)
>>
>>Here , "Project__id" field of the "employee" table is the foreign key
>>referencing the "id" field of "project" table . Following are the
>>corresponding classes for the two tables
>>
>>
>>//Only attributes are shown here
>>public class Employee {
>>
>> private String id ;
>> private String name;
>> private Integer age ;
>> private Project project ;
>> private String projectId ;
>>}
>>
>>public class Project
>>{
>>
>> private String id ;
>> private String name ;
>> public Vector allEmployeesInProject ;
>>
>>}
>>
>>I have defined the relationship in repository_user.xml files for both
>>the classes .
>>
>>I want to make the following query ......
>>
>> Select e.id , e.name , e.age , p.name from employee e, project p
>> where e.project__id= p.id and
>> e.age > ( select avg(age) from employee )
>>
>>I am using the following code for the same .
>>********************************************
>> subQuery = new ReportQueryByCriteria(Employee.class, subCriteria);
>> criteria.addGreaterThan("age" , subQuery) ;
>> mainQuery = new ReportQueryByCriteria(Employee.class, criteria);
>> mainQuery.setAttributes(new String[] { "id" , "name" , "age" ,
>
> "project.name" });
>
>> subQuery.setAttributes(new String[] { "avg(age)" });
>> iterator = broker.getReportQueryIteratorByQuery(mainQuery);
>> System.err.println("Id " + "||" + "Name " + "Age " + "||" + "
>
> Project Name ") ;
>
>> while(iterator.hasNext())
>> {
>> Object result[] = (Object[])(iterator.next());
>> System.err.println( (String)result[0] + "||" + (String)result[1] +
>
> "||"+ ((BigDecimal)result[2]) + "||" + (String)result[3] ) ;
>
>>}
>>*********************************************************
>>
>>I the log file spy.log I can see that the query generaed is exactly
>>that I want .And it is producing the correct results when I run it on my
>
> data base
>
>>But , I am facing a strange problem in the "column 4th"(project.name) when
>
> I try to print the result in stderr .
>
>>The 4th element ie the result[4] is showing the Name of the "employee"
>
> and not the name of the "project" .
>
>>When I copy the query from the log file and run it in its running the
>
> correct result ie the last column is the the project.name .
>
>>Also , When I select "project.id" instead of "project.name" . I am getting
>
> the id of the projects , ie the correct results .
>
>>Please Help me .
>>Thanks in advance .
>>
>>Chandan Ahuja
>>
>>
>>
>>
>>---------------------------------------------------------------------
>>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
>
>
>
> ---------------------------------------------------------------------
> 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