if you are going to use a shadow table, how about a trigger on the
original table that parses the field into separate columns and does an
insert into the shadow table? Update if necessary (not all that
difficult, just replace all the parsed fields in case) and delete,
depending on the types of dml done on the original table.
not elegant but it would give you the normalized table.
- DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> wrote:
> Kirti - We have a denormalized table like this in one database. An
> excellent
> moral lesson for those who doubt the wisdom of normalization.
> My first choice would be to lobby to redesign this table. The longer
> it remains and the more programs are built around this design, the
> more
> painful the eventual redesign will be.
> My second choice would be something along the lines of a shadow
> table to move all the records periodically for reporting. If you need
> to
> search on these fields, you could create a function-based index on
> each
> column. One idea that occurs to me that I haven't tested would be
> whether
> you could write a stored procedure that would parse the field and
> return the
> columns. This would be usable by any utility that could execute a
> PL/SQL
> procedure.
> Kirti - you have my sympathy.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 2:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Thanks.
> Substr/instr was rejected because it was a bit difficult to read the
> code.
>
> Also, they wanted to extract the fields in their own column headings
> (new
> requirement). So 'replace' may not fly much !!
>
> - Kirti
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 1:45 PM
> To: Multiple recipients of list ORACLE-L
>
>
> What about
>
> select commission_id, replace(com_text_msg,'~',chr(9))
> from tab1
>
> which would work if going to a tab separated file for something like
> excel.
>
>
> Whats wrong with substr/instr?
>
> Iain Nicoll
>
> -----Original Message-----
> Sent: Wednesday, May 29, 2002 7:22 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I need some help...
>
> The database table has following structure.
> commision_id number
> com_text_msg varchar2(500)
>
> The second column contains data fields that are delimited by ~ and
> delimiter's position varies. But there are only eight data fields in
> the
> column.
>
> Is there a way in SQL, other than substr/instr combinations, to
> extract each
> data field to report?
>
> Thanks.
>
> - Kirti
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Nicoll, Iain (Calanais)
> INET: iain.nicoll_at_calanais.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_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 May 29 2002 - 15:08:41 CDT
