| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: what is wrong with this idea ...
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
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: ListGuru_at_fatcity.com (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).
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: ListGuru_at_fatcity.com (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). Received on Wed Aug 14 2002 - 09:03:42 CDT
 |
 |