Hello All
I have a phone table
and an employee table. The phone table has a foreign key
constraint on the employee id. An employee can have multiple
phones.
Now when I query for a
employee having phone number say '11' I set the selection criteria
as:
query.setSelectionCriteria(
query.getExpressionBuilder().anyOf("empinfo.phone.empid").get("phone").equal("11"));
I add the following
join attribute:
query.addJoinAttribute(query.getExpressionBuilder().anyOf("empinfo.phone.empid"));
Case 1: If I set
the selection criteria before adding the join attribute the query
generated is:
select * from empinfo t0, phone
t1 where t0.empid=t1.empid and t1.phone='11'
which does not return
all the phone numbers of the employee having phone number '11'.
Case 2: If I set
the selection criteria after adding the join attribute the query
generated is:
select * from empinfo t0, phone
t1, phone t2, where t0.empid=t1.empid and t1.phone='11' and
t2.empid=t0.empid
which returns all the
phone numbers of the employee having phone number '11'.
Also in case 1 if I
generate the query for the second time with the same session, the
query of case 2 is generated, however again not all phone numbers
of the employee having phone number '11' are generated (perhaps
due to caching?)
If I do not set any
selection criteria the first time I query, then all the
information is returned the first time as expected. If I now use
the sequence of Case 1 for creating a query in the same session
then the correct result is obtained. How to explain this?
Is this behavior
expected? Why is it important to add the join attribute before
setting the selection criteria?
Could you please
point me to the relevant documentation?
Thanks in Advance!
--
Thanks and Regards
Rohit Banga
Member Technical Staff
Oracle Server Technologies