Oracle newbie question

[groundclutter]

Hi and thanks for reading.

<b><u>Problem:</u></b>
Our current Oracle8 database schema has a table, e.g. "header", that has two related detail tables, e.g. "detail1" and "detail2". They are related in the following way:

<i>header.key --> detail1.header_key</i> and
<i>header.key --> detail2.header_key</i>

When developing reports (in Crystal Reports 8.5), the query brings back the following dataset:

<i>header.*, detail1.*, and detail2.*</i> but the details are never in the same row since they are exclusive resulting in two rows for every one header.

The biggest problem is when the requirement is to group by one "detail1" field and one "detail2" field. What happens is that the details are split out and then grouped with basically duplicates the header.

<b><u>Question:</u></b>

What I am looking to do is create a dynamic view (or should i be using a stored procedure) that would collect the data as one row (on the server side) and then I could use that view (or temp table) as my data source for the report.

I have gotten into SQL*Plus but I am definitely new to this utility so if I need to code something, please provide a simple instruction as to how to initiate new procedure creation and as to how to save, etc.

Thanks again for reading and offering any assistance.

Clutter

 

[Turkbear]

Hi,
a view should be fine using subqueries ( inline views):
I will assume 2 fields besides the key in each detail record.

Try ( cannot test so it may need tweeking):

Create or replace view HeadandDetail as
select h.header.key headkey,det1.detail.field1 det1F1 ,det1.detail1.field2 det1Fld2,det2.detail2.field1 det2Fld1,det2.detail2.field2 det2Fld2
from
(select detail.field1 ,detail1.field2 from detail1 where 
  header.key = detail1.key) det1 ,
(select detail.field1 ,detail1.field2 from detail2 where 
  header.key = detail2.key) det2,
header h
where header.key = 'xxxx';

Should be a start at least

 

[SantaMufasa]

Clutter,

...and if you want a traditional VIEW instead of in-line views, here is some code:

Section 1 -- Base tables:

SQL> select * from header;

     H_KEY
----------
         2
         1
         3
SQL> select * from detail1;

HEADER_KEY TXT
---------- ----------
         1 Det 1 Text
         3 Det 1 Text
SQL> select * from detail2;

HEADER_KEY TXT
---------- ----------
         1 Det 2 Text
         3 Det 2 Text

Section 2 -- View create:

SQL> create or replace view HD_D1_D2 (HD_Key, D1_Text, D2_Text) as
  2  select H_Key, d1.txt, d2.txt
  3  from header,detail1 d1, detail2 d2
  4  where h_key=d1.header_key
  5  and h_key=d2.header_key
  6  /

Section 3 -- Accessing the View:

SQL> select * from hd_d1_d2;

    HD_KEY D1_TEXT    D2_TEXT
---------- ---------- ----------
         1 Det 1 Text Det 2 Text
         3 Det 1 Text Det 2 Text
SQL>

 

[Turkbear]

Hi,
As always there is more than one way to skin a cat



(still tastes like chicken, though)