Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Retrive records from Oracle (8i) tables !!

SOLVED
Go to solution
Chris Fung
Frequent Advisor

Retrive records from Oracle (8i) tables !!

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,
3 REPLIES
Hein van den Heuvel
Honored Contributor
Solution

Re: Retrive records from Oracle (8i) tables !!

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
Frequent Advisor

Re: Retrive records from Oracle (8i) tables !!

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
Honored Contributor

Re: Retrive records from Oracle (8i) tables !!

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