Retrive records from Oracle (8i) tables !!

[Chris Fung]

Hi there,

I have 2 tables to keep track the software asset information. One is called software_installed (sw_installed_id, hw_id, sw_category, sw_subcategory, sw_version, coe_id)

Where sw_installed_id is the primary key and coe_id is the foreign key point to "coe_details" table

Another table called coe_details (coe_id, project_name, coe_description, sw_category, sw_subcategory, sw_version)

Where the primary key is the composed of the following fields i.e. (coe_id, sw_category, sw_subcategory, sw_version).

coe stands for "Common Office Environment". Both tables will have sw_category, sw_subcategory and sw_version fields. I am just wondering whether it is possible (and how) to create a conditional cartesian product for them.

As such, those rows in sw_installed table with coe_id field equals to Null will return the current rows in sw_installed.

While those rows in sw_installed table with coe_id values in coe_details table will return the rows from coe_details table.

Any idea for creating a view to store the results set that I described above ?

Appreciated for your recommendations.

Cheers,

Chris,

[Hein van den Heuvel · Accepted solution]

Hmmm,
I think you want a UNION or maybe an OUTER JOIN, not a cartesian (too big).
I create a mini dataset similar to your
description. Below that set, and a union sample. Attached the same (more readable spacing, and an outerjoin approach. I only tried on 9.0.2

Does that help?
Hein.


SQL> select * from software_installed;

SW_INSTALL SW_CATEGOR COE_ID
---------- ---------- ----------
ins 1 cat 1 coe 1
ins 1 cat 1
ins 2 cat 2 coe 4
ins 2 cat 2

SQL> select * from coe_details;

COE_ID PROJECT_NA SW_CATEGOR
---------- ---------- ----------
coe 1 proj 10 cat 10
coe 4 proj 14 cat 14
coe 4 proj 15 cat 15
SQL> list
1 select sw_installed_id "sw", coe_id "id", sw_category "cat"
2 from software_installed where coe_id is not NULL
3 union
4 select s.sw_installed_id "sw", s.coe_id "id", d.sw_category "cat"
5 from software_installed s, coe_details d
6 where s.coe_id = d.coe_id
7* order by 1
SQL> /

sw id cat
---------- ---------- ----------
ins 1 coe 1 cat 1
ins 1 coe 1 cat 10
ins 2 coe 4 cat 14
ins 2 coe 4 cat 15
ins 2 coe 4 cat 2

[Chris Fung]

Hello Hein,

Thank you very much for your input, it really helps me solving my problem.

I made a little change on "Union" to "Union All" and get the results what I want.

Cheers,

Chris,

[Hein van den Heuvel]

You're welcome.
After i submitted I realized I cut & paste proably the wrong test, and needed 'is NULL' instead of the 'is NOT NULL', but it looks like it was close enough to get you going...

SQL> list
1 select sw_installed_id "sw", coe_id "id", sw_category "cat"
2 from software_installed where coe_id is NULL
3 union
4 select s.sw_installed_id "sw", s.coe_id "id", d.sw_category "cat"
5 from software_installed s, coe_details d
6 where s.coe_id = d.coe_id
7* order by 1
SQL> /

sw id cat
---------- ---------- ----------
ins 1 coe 1 cat 10
ins 1 cat 1
ins 2 coe 4 cat 14
ins 2 coe 4 cat 15
ins 2 cat 2

Cheers.
Hein