Re: how to scan text in column and use to join to another table

On Dec 21, 5:30 pm, DA Morgan <damor..._at_psoug.org> wrote:
> D wrote:
> > Hello everyone -
> > I have a table "IDST_Customers" which contains rate codes that
> > are associated with their accounts. On a separate table
> > "IDST_RATE_REPORTING_CENTER" contains the reporting center that the
> > assigned rate codes belongs to. If I want to join the two tables on
> > rate code criteria there is a field in the 2nd table
> > Rate_Reporting...titled DRATECODE - which contains 1 rate code value
> > per record. In my original table Customers...I have a field titled
> > FAST_RATES which is a concatenation of all rate codes assigned to this
> > account. So if I want to match one of those rate codes in fast_rates to
> > DRATECODE...how do I do this? Sorry for the redundancy, and as a side
> > note - I am not able to create temp tables on this server. Thanks!You redesign the application. Concatenating codes together is an
> inefficient approach that leads to nightmares when trying to work
> with the data.
>
> Create a child table to hold that information connected back to the
> parent via a foreign key.
>
> If you can't do that ... then post sample contents of the concatenated
> field and your Oracle version number.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Here is a sample that I used in a view for my customer. The serial number is used as a single item in 99.985% of our world but in one table out of a couple thousand a developer created it as three columns.

select df.*, wm.*

    from owner.defects_at_remote.world df,

         wo_master wm

   where df.unit_pre    = substr(wm.serial_no,1,2)
     and df.unit_series = substr(wm.serial_no,3,2)
     and df.unit_nbr    = substr(wm.serial_no,5,6)

For efficiency the user was instructed to always provide the full serial number to the view so it drives on an index to get a row then selects across a database link to access the remote table via an index.

As Daniel stated you do not want to do this as a rule.

HTH -- Mark D Powell -- Received on Thu Dec 21 2006 - 18:45:12 CST