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" <NDATFM_at_labor.state.ny.us>
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:gurelei_at_yahoo.com]
> 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: gurelei_at_yahoo.com
>
> 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).
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: gurelei_at_yahoo.com
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:23:45 CDT
