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 Go to next message
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 #410254 is a reply to message #410253] Thu, 25 June 2009 21:34 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
What do you mean by
Quote:
Is there a way not to repeat the contact type, personnel type, first and last name?
You may need to post a test case with DMLs and DDLs.

By
Vamsi
Re: help with inline view query [message #410256 is a reply to message #410254] Thu, 25 June 2009 21:44 Go to previous messageGo to next message
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 #410259 is a reply to message #410256] Thu, 25 June 2009 22:05 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use BREAK.

By
Vamsi
Re: help with inline view query [message #410260 is a reply to message #410256] Thu, 25 June 2009 22:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 Go to previous message
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
Previous Topic: query pivot with flag
Next Topic: analytical functions
Goto Forum:
  


Current Time: Sat Dec 03 10:16:41 CST 2016

Total time taken to generate the page: 0.07486 seconds