Challenging SQL*Plus Reporting Problem

From: Jeff Busch <busch1_at_llnl.gov>
Date: 1997/02/17
Message-ID: <5eac5p$5f5_at_lll-winken.llnl.gov>#1/1

Thanks Brian. Unfortunately your suggestion does not work because there is no relationship between author and company and, as you point out, queries joining the tables return multiple rows.

That's the problem for which I was hoping to find a solution.

-horsemth_at_mel.switch.net.au> writes:
-The formatting can be achieved quite easily by including a BREAK statement
-in the SQL to remove duplicate titles and give the space line ...
-
-BREAK ON PUB_TITLE NODUP SKIP 1
-
-I'm not sure what you're trying to achieve because the data model does not
-link COMPANY to AUTHOR and any attempt to select will cause extra rows 

-thus 
-> I have three tables:
-> 
-> Publication:                   Company:               Author:
-> pub_id_no varchar2(7)   pub_id_no varchar2(7)         pub_id_no
-varchar2(7)
-> pub_title varchar2(20)  pub_company_name varchar2(20) pub_author_name
-varchar2(20)
-> pub_date  date
-> 

>> Each publication can have 0 through N companies
>> Each publication can have 0 through N authors
>>
>> I'd like a report like this:
>>
>> Publication Title Company Author
>>
>> Title #1 1st company of pub 1 1st author of pub
 1
>> 2nd company of pub 1 2nd author of pub
 1
>> Nth company of pub 1 nth author of pub
 1
>>
>> Title #2 etc.
>>
>> Is there any way to do this is SQL*Plus?
>>

---
Jeff Busch             Internet busch1_at_llnl.gov
Voice (510) 423-9134   Fax (408) 263-1360

Received on Mon Feb 17 1997 - 00:00:00 CET