Select whole "Objects"

From: jlaffer <jerry_at_everymail.net>
Date: 14 Feb 2006 05:50:33 -0800
Message-ID: <1139925033.494429.259740@f14g2000cwb.googlegroups.com>

Hi there,
can anybody help me in this matter please?

PROBLEM DESCRIPTION: The problem I have is kind of a "object"-related matter. Imagine a client application, that allows the user to search the application's objects (stored relational in Oracle) by providing filter criteria. The backend-application (java btw.) tries to generate the SQL-Conditions and appends them to a predefined Query that selects all necessary data (all Objects).

Basically the desired functionality is this: "Never split an Object's data"
Sounds simple... nevertheless I wasn't able to implement it with reasonable effort.

Example: the data of the Company-Object is stored in two tables.. (In reality both tables are relatively complex queries, containing lots of joins)

o) Table COMPANY with the column ID
o) Table EMPLOYEE, containing the column COMPANY_ID and for example the column EMPLOYEE_NAME.

We now think of COMPANY as an Object, consisting of Attributes that may only occur once (the columns in the COMPANY table) and some Attributes that may occur more often (the columns in the EMPLOYEE table).

The user has the possibility to search these objects via a client-application. The application allows to filter the companies data, using any of the companies attributes. So, if the user configures the filter "EMPLOYEE_NAME must not be JOHN DOE", the search result should show only companies, which don't have an employee named "JOHN DOE". Talking SQL it would look like:
SELECT * FROM
(

  SELECT c.ID COMPANY_ID


        ,c.NAME COMPANY_NAME

        ,e.NAME EMPLOYEE_NAME

    FROM COMPANY c


        ,EMPLOYEE e

   WHERE c.COMPANY_ID = e.COMPANY_ID (+)

) company
WHERE company.COMPANY_ID IN
(
SELECT e.COMPANY_ID FROM EMPLOYEE e WHERE e.NAME != 'JOHN DOE' )

Here you can see problem one. I need a subselect to get the correct result, which i have to configure somewhere for each possible filter-criteria in tables with a 1:n connection to the main table (in this case COMPANY). I would have very much liked to use a simple SQL Condition (WHERE X != Y) to keep the automatic generation simple.

If the user decides to additionally define the filter "EMPLOYEE_NAME is JANE DOE" the statement would look like:

SELECT * FROM
(

  SELECT c.ID COMPANY_ID


        ,c.NAME COMPANY_NAME

        ,e.NAME EMPLOYEE_NAME

    FROM COMPANY c


        ,EMPLOYEE e

   WHERE c.COMPANY_ID = e.COMPANY_ID (+)

) company
WHERE company.COMPANY_ID IN
(
SELECT e.COMPANY_ID FROM EMPLOYEE e WHERE e.NAME != 'JOHN DOE' INTERSECT
SELECT e.COMPANY_ID FROM EMPLOYEE e WHERE e.NAME = 'JANE DOE' )

To get the desired result, UNION, MINUS and INTERSECT operations have to be performed.
For each filter-criteria the user defines, the whole subselect has to be inserted into the SQL-Statement. As the user also has the possibility to use brackets "(" and operators like 'OR' to connect two filter conditions, the statement gets very big and complex.

QUESTION: Is there another way to tell oracle which lines of a result-set belong to "one object" and must not be split (selected partially)?

In other words, is there a possibility to make it look like this?: SELECT * FROM COMPANY_OBJECTS
WHERE EMPLOYEE_NAME != 'JOHN DOE'
AND EMPLOYEE_NAME = 'JANE DOE' Any ideas?

Thanks very much in advance for your answers.. any input is welcome.

Have a nice day,
Jerry Laffer Received on Tue Feb 14 2006 - 07:50:33 CST