| 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: SQL Question
Dennis,
Thanks for the ideas, but...
This is what happens when someone decides to write their own reports against tables that were not designed by themselves. Damagement thinks it is just a simple SELECT !!!
Looks like they just will have to live with unreadable SQL !
Thanks.
- Kirti
-----Original Message-----
Sent: Wednesday, May 29, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L
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 !!
-----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.
--
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).
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).
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 May 29 2002 - 15:00:18 CDT
 |
 |