Re: Help Needed for Report Query Problem .

[Jakob Braeuchi]

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:[EMAIL PROTECTED] 
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: [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]