Gene,
You ask what's wrong with this? The main challenge I see is: how are you
going to maintain this field? If a status gets updated in the child table,
are you supposed to update the parent table? And to do that, you will need
to requery the child table to get all the status's of all children records
whenever one status changes.
Pretty silly really,
Why not do this - create a function that, given the PK of the parent table,
queries all the child records and concatenates all the status values into
one string. Then either create a view for the duhveloper to use to return
what he wants to return at query time.
Something like this:
Create or replace function get_child_status(in_pk_column) return varchar2 is
cursor c1 is
select status_code
from child_table
where pk_column=in_pk_column;
ret_string varchar2(10);
begin
for c1_rec in c1 loop
ret_string := ret_string || c1_rec.status_code;
end loop;
return ret_string
end;
The duhveloper can then:
select parent_pk_column, get_child_status(parent_pk_column)
from parent_table;
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, August 14, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L
Hi. We have a table in our data warehouse which keeps
info about calls made. This table has a child table
with some detailed information about parts of the
call. There may be any number of "parts" within a call
(1 to many) and every part has a status.
MY developer wants to add a string field to the parent
table which will concatinate all the statuses for
all the parts within this call. For example if
a call has 4 parts and their statuses are "A","B","A"
and "F", the value of that field will be "ABFA". Then
the developer will be able to query smalle parent
table instead of a large child table in order to see
how many calls had at least one part with status "A"
or statuses "A" and "F" etc by using a INSTR (or
SUBSTR) command.
Would it be better (from performance/CPU standpoint)
to add several separate fields: STATUS_A_CNT,
STATUS_B_CNT (the list of status codes is fairly
static) instead? There is something about this string
that rubs me the wrong way, but I can't put my finger
on it.
Any thoughts?
thank you
Gene
__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gurelei
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
