This discussion is archived
6 Replies Latest reply: Jan 20, 2013 1:10 PM by Johannes M RSS

DB-Adapter 10g: Filtering Detail-Rows in Master-Detail-Relationship

Johannes M Newbie
Currently Being Moderated
Hi,

I'm having a Master-Detail-Table e.g. Departments and Employees. Those are mapped with 1:N FK-Relationship. Using the Oracle SOA-Suite 10.1.3.5 DB-Adapter I'd like to setup a filter returning all Departments and all (e.g.) female Employees.

For the example see: http://docs.oracle.com/cd/E11036_01/integrate.1013/b28994.pdf (chapter 4.3.9). Unfortunately, this doesn't work. If definining a where-clause empCollection.gender='female' I get the following behavior:
- If a dept has no female emp, this department is not shown at all (however I want it to be empty)
- If there is at least one female emp, the department and all employees are shown.


Any idea on how to solve this problem would be highly appreciated!

Regards,
Johannes
  • 1. Re: DB-Adapter 10g: Filtering Detail-Rows in Master-Detail-Relationship
    Johannes M Newbie
    Currently Being Moderated
    Well I tought about 2 ideas for solving this issue that I all don't really like:

    1) I could of cause do the detail-selects manually. but this would lead to a highly increased number of database-queries, so I don't really like this idea. Additionally, it would require some additional programming effort fiddling master and detail together (in the real scenario its more a master-detail1-detail2-detail3-detail4 relation with filters on detail3 and detail4.

    2) I could change the selects from a table to a view and initially set some session-variable that my views query. this initial setting could be done using a pl/sql-package-call. But I'm not sure if this will work at all, additionally I don't like the idea of such a session variable since I'm not sure if this will work with multiple concurrent queries

    Any further ideas? Would it help to change the fetch-mode of the details in the toplink-mapping? How exactly would this have to be done?


    Regards,
    Johannes
  • 2. Re: DB-Adapter 10g: Filtering Detail-Rows in Master-Detail-Relationship
    vladodias Guru
    Currently Being Moderated
    Hi,

    I just think your query is wrong, you are selecting all departments that have at least one female employee... It seems that what you want is to select all female employees, with their respective departments + the departments with no female employees...

    You have to make toplink generate a sql like bellow... with an outer join...
    select department.name, employee.name
    from employee right outer join department on employee.department_id = department.department_id
    where employee.gender = 'female'
    Hope this helps...

    Cheers,
    Vlad
  • 3. Re: DB-Adapter 10g: Filtering Detail-Rows in Master-Detail-Relationship
    Johannes M Newbie
    Currently Being Moderated
    Thanks for your help. I'm not sure where to enter that query. Into the last step of the database adapter configuration wizard? What columns would I have to select or does that not count here?

    Or has this to be adjusted in the toplink mapping itself (in the query section)? I'm not sure if this would automatically be used from the adapter then.


    Regards,

    Orgler
  • 4. Re: DB-Adapter 10g: Filtering Detail-Rows in Master-Detail-Relationship
    Johannes M Newbie
    Currently Being Moderated
    I gave it a try, 2 problems:


    1) the outer-join seems to be wrong. it provides me only departments that have at least one female employee. If I change that outer-join to:
    select DEP.id,DEP.name, EMP.id,EMP.GENDER
    from EMP, DEP
    where EMP.DEP_ID(+)=DEP.id
    and emp.gender(+)='female'

    it gives me what I want from sqldeveloper:
    select * from emp;
    ID NAME
    ---------- --------------------
    1 empty
    2 mixed
    3 male
    4 female

    select * from dep;
    ID DEP_ID GENDER
    ---------- -------------------- --------------------
    1 2 male
    2 2 female
    3 2 male
    4 3 male
    5 3 male
    6 4 female
    7 4 female
    8 4 female

    select DEP.id,DEP.name, EMP.id,EMP.GENDER
    from EMP, DEP
    where EMP.DEP_ID(+)=DEP.id
    and EMP.GENDER(+)='female'

    ID NAME ID GENDER
    ---------- -------------------- ---------- --------------------
    1 empty
    2 mixed 2 female
    3 male
    4 female 6 female
    4 female 7 female
    4 female 8 female




    2) However that doesn't solve my problem with the BPEL-Adapter. There I still get the folowing result:

    <DepCollection xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/top/test4" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Dep>
    <id>2</id>
    <name>mixed</name>
    <empCollection>
    <Emp>
    <id>1</id>
    <gender>male</gender>
    </Emp>
    <Emp>
    <id>2</id>
    <gender>female</gender>
    </Emp>
    <Emp>
    <id>3</id>
    <gender>male</gender>
    </Emp>
    </empCollection>
    </Dep>
    <Dep>
    <id>4</id>
    <name>female</name>
    <empCollection>
    <Emp>
    <id>6</id>
    <gender>female</gender>
    </Emp>
    <Emp>
    <id>7</id>
    <gender>female</gender>
    </Emp>
    <Emp>
    <id>8</id>
    <gender>female</gender>
    </Emp>
    </empCollection>
    </Dep>
    <Dep>
    <id>3</id>
    <name>male</name>
    <empCollection>
    <Emp>
    <id>4</id>
    <gender>male</gender>
    </Emp>
    <Emp>
    <id>5</id>
    <gender>male</gender>
    </Emp>
    </empCollection>
    </Dep>
    <Dep>
    <id>1</id>
    <name>empty</name>
    <empCollection />
    </Dep>
    </DepCollection>

    This is how I used the wizard:

    1_Select_Table.png

    2relationship.png

    3objectfiltering.png

    4selection_Criteria.png

    Any futher ideas?

    Edited by: Johannes M on 17.01.2013 12:31

    Edited by: Johannes M on 17.01.2013 12:34
  • 5. Re: DB-Adapter 10g: Filtering Detail-Rows in Master-Detail-Relationship
    vladodias Guru
    Currently Being Moderated
    Problem is you're still getting a DepCollection... If you navigate the relationship from Dep -> Employee you won't be able to filter employees... To have the results you want you need to make the adapter give you a Employee Collection, navigating from Employee -> Dep...

    Hope this helps...

    https://forums.oracle.com/forums/ann.jspa?annID=893
  • 6. Re: DB-Adapter 10g: Filtering Detail-Rows in Master-Detail-Relationship
    Johannes M Newbie
    Currently Being Moderated
    Hi,

    Ok of cause this would be a solution. However in my real scenario I'll have to stick to a structure with departments as the root elements.

    So I'll then have to switch to one of my proposed workarounds? Which would you propos ?



    Regards,
    Orgler

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points