Home » SQL & PL/SQL » SQL & PL/SQL » help with inline view query (8i oracle enterprise)
| help with inline view query [message #410253] |
Thu, 25 June 2009 21:24  |
bztom33
Messages: 95
Registered: June 2005
|
Member |
|
|
The following tables are joined via se_contact_id. For each contact in se_contact table, there are potentially multiple address,email and phone. Is there a way not to repeat the contact type, personnel type, first and last name?
Thanks,
---tables--------
se_contact
se_contact_email
se_contact_phone
se_contact_address
=== lookup tables for contact type,position type (personnel), email,address,phone---
se_contact_type_lookup
personnel_type_lookup
email_type_lookup
phone_type_lookup
address_type_lookup
SELECT se_contact_type_desc ,
personnel_type_name ,
se_contact.se_first_name ,
se_contact.se_last_name ,
a.se_address1 ,
a.se_address2 ,
a.se_city ,
a.state_id ,
a.se_zipcode ,
NVL(p.phone_type,'No Label')Type ,
NVL( p.se_phone_num,'No Phone')Phone ,
NVL(p.se_phone_ext,'No Extension')Extension,
NVL(e.email_type_desc,'N/A')Type ,
NVL(e.se_email_addr,'No Email') Email
FROM se_contact ,
(SELECT se_contact_id,
phone_type ,
se_phone_num ,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
)p ,
se_contact_address a ,
op_contacts s ,
(SELECT se_contact_id,
email_type_desc ,
i.se_email_addr
FROM se_contact_email i,
email_type_lookup u
WHERE i.email_type_id = u.email_type_id
)e ,
personnel_type_lookup per,
se_contact_type_lookup secup
WHERE se_contact.se_contact_id = p.se_contact_id(+)
AND se_contact.se_contact_id = a.se_contact_id(+)
AND se_contact.se_contact_id = s.se_contact_id(+)
AND se_contact.se_contact_id = e.se_contact_id(+)
AND se_contact.personnel_type_id = per.personnel_type_id
AND se_contact.se_contact_type_id = secup.se_contact_type_id
ORDER BY se_contact.se_last_name
[EDIT by VK: what is the last AND. Didn't it give you an error?]
[Updated on: Thu, 25 June 2009 22:21] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: help with inline view query [message #410256 is a reply to message #410254] |
Thu, 25 June 2009 21:44  |
bztom33
Messages: 95
Registered: June 2005
|
Member |
|
|
currently I am getting the following output.
Mail Person,Manager,Joe Doe,3312 abc street, bus phone:916-323-2342
Mail Person,Manager,Joe Doe,3312 abc street, cell:916-234-2343
Mail Person,Manager,Joe Doe,3312 abc street, home:916-234-2341
Mail Person,Manager,Joe Doe,3312 abc street, fax:916-234-2345
I want to have the following output:
Mail Person,Manager,Joe Doe,3312 abc street, bus phone:916-323-2342
cell:916-234-2343
home:916-234-2341
fax:916-234-2345
|
|
|
|
|
|
| Re: help with inline view query [message #410260 is a reply to message #410256] |
Thu, 25 June 2009 22:10 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I do no think your example is correct. Your data does not reflect your statement. It is highly misleading for you to post with an example where there is only one of each type of contact. Find an example with two busines phones, two cells, two home phones, and two faxes. You do understand that this example will produce sixteen (16) rows right?
Restate your question in light of the sixteen rows.
Next, when you say you do not want to repeat the header data, in what context? Do you mean you do not want to see it in the data, or that when you report on it you want the visual output to show it only once. If the later than use the break command in sql plus to break on all your header items to supress duplicate values. Use a similar command in other reporting tools.
If you mean in the data then the answer is NO and YES.
No, you cannot retrieve only partial rows in your output.
Yes, you can change your output to produce only one row for each set of header values. But if you do this it is not the same output of course. Three obvious examples come to mind, COLLECTIONS, CURSOR EXPRESSIONS, and XML.
With collections, you can define your multiple rows sections and independent collections, one for business phones, one for cell phones, one for home phones, one for fax. Then you can retrieve each set of phones as a collection housed in a single column.
With cursor expressions you do the same thing except that you code a cursor expression as one of your columns rather than getting your rows as a collection in that column. It is the same rowsset, just returned as a REFCURSOR rather than a strongly typed collection.
With XML you do one of the two above and then return it as XML (try DBMS_XMLGEN.GETXML). Or, your write and XML query using Oracle's XML functions and effectively do the same thing only with much more coding.
Here is an example of cursor expressions, they are the easiest of the three to make work. Since you did not provide test case setup sql, I do not warrent the correctness of this solution, or that it will even compile. It is for illustrative purposes.
SELECT se_contact_type_desc,
personnel_type_name,
se_contact.se_first_name,
se_contact.se_last_name,
a.se_address1,
a.se_address2,
a.se_city,
a.state_id,
a.se_zipcode,
(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'bus phone') business_phone_list
,(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'home') home_phone_list
,(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'cell') cell_phone_list
,(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'fax') fax_phone_list
,(SELECT se_contact_id,
email_type_desc,
i.se_email_addr
FROM se_contact_email i,
email_type_lookup u
WHERE i.email_type_id = u.email_type_id
and se_contact.se_contact_id = e.se_contact_id) email_list
FROM se_contact,
se_contact_address a,
op_contacts s,
personnel_type_lookup per,
se_contact_type_lookup secup
WHERE se_contact.se_contact_id = a.se_contact_id (+)
AND se_contact.se_contact_id = s.se_contact_id (+)
AND se_contact.personnel_type_id = per.personnel_type_id
AND se_contact.se_contact_type_id = secup.se_contact_type_id
ORDER BY se_contact.se_last_name
Good luck, Kevin
|
|
|
|
| Re: help with inline view query [message #410280 is a reply to message #410260] |
Thu, 25 June 2009 23:48 |
bztom33
Messages: 95
Registered: June 2005
|
Member |
|
|
Thanks for your quick response, but it give ORA-00913: too many values.
If I removed the problem section, it will return something, but it's not the expected output.
SELECT se_contact_type_desc,
personnel_type_name,
se_contact.se_first_name,
se_contact.se_last_name,
a.se_address1,
a.se_address2,
a.se_city,
a.state_id,
a.se_zipcode,
---problem section------------
(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'bus phone') business_phone_list
,(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'home') home_phone_list
,(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'cell') cell_phone_list
,(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'fax') fax_phone_list
,(SELECT se_contact_id,
email_type_desc,
i.se_email_addr
FROM se_contact_email i,
email_type_lookup u
WHERE i.email_type_id = u.email_type_id
and se_contact.se_contact_id = e.se_contact_id) email_list
------problem section end
FROM se_contact,
se_contact_address a,
op_contacts s,
personnel_type_lookup per,
se_contact_type_lookup secup
WHERE se_contact.se_contact_id = a.se_contact_id (+)
AND se_contact.se_contact_id = s.se_contact_id (+)
AND se_contact.personnel_type_id = per.personnel_type_id
AND se_contact.se_contact_type_id = secup.se_contact_type_id
ORDER BY se_contact.se_last_name
|
|
|
|
| Re: help with inline view query [message #410343 is a reply to message #410280] |
Fri, 26 June 2009 07:38 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Yes, sorry this is my mistake. Cursor expressions requires the keywork CURSOR which I forgot in the code. Try doing a google on the concept to learn more. Here is the correcttion:
SELECT se_contact_type_desc,
personnel_type_name,
se_contact.se_first_name,
se_contact.se_last_name,
a.se_address1,
a.se_address2,
a.se_city,
a.state_id,
a.se_zipcode,
---problem section------------
CURSOR(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'bus phone') business_phone_list
,CURSOR(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'home') home_phone_list
,CURSOR(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'cell') cell_phone_list
,CURSOR(SELECT se_contact_id,
phone_type,
se_phone_num,
se_phone_ext
FROM se_contact_phone x,
phone_type_lookup l
WHERE x.phone_type_num = l.phone_type_num
and se_contact.se_contact_id = x.se_contact_id
and l.phone_type = 'fax') fax_phone_list
,(SELECT se_contact_id,
email_type_desc,
i.se_email_addr
FROM se_contact_email i,
email_type_lookup u
WHERE i.email_type_id = u.email_type_id
and se_contact.se_contact_id = e.se_contact_id) email_list
------problem section end
FROM se_contact,
se_contact_address a,
op_contacts s,
personnel_type_lookup per,
se_contact_type_lookup secup
WHERE se_contact.se_contact_id = a.se_contact_id (+)
AND se_contact.se_contact_id = s.se_contact_id (+)
AND se_contact.personnel_type_id = per.personnel_type_id
AND se_contact.se_contact_type_id = secup.se_contact_type_id
ORDER BY se_contact.se_last_name
You should know what you are getting into with this stuff. Consider the difference between these two statements:
SQL> select * from dual;
D
-
X
1 row selected.
SQL> select cursor(select * from dual) from dual;
CURSOR(SELECT*FROMDU
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
D
-
X
1 row selected.
1 row selected.
Kevin x79427
|
|
|
|
| Re: help with inline view query [message #410443 is a reply to message #410343] |
Sat, 27 June 2009 01:24 |
bztom33
Messages: 95
Registered: June 2005
|
Member |
|
|
Thanks for your help, Kevin.
It returns the results that I expected to see
one line per contact with all the address, phones and email.
I know it will be a performance issue for large query. Typically, it is less than 100 rows per query.
You also mentioned Collection on your last post. Does it have better perform if you know or do not know your upper limit on the number of return rows.
Tom
|
|
|
|
Goto Forum:
Current Time: Tue Nov 18 18:50:10 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
