Tom, The child table is not going to be updated ad-hoc. As I said this is a data warehouse and there is no ad-hoc updates. Only one load during the day and during this load that field is going to be populated. The thing I don't like about the procedure is that it will be doing a query against a large table every time the developer runs a query using that procedure. By loading that field(d) during the load we only do it once.
--- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote: > 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----- > From: Gurelei [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, August 14, 2002 9:19 AM > To: Multiple recipients of list ORACLE-L > Subject: what is wrong with this idea ... > > > 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). __________________________________________________ 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).
