Re: Building a lookup table

In article <01be7f1d$abe14940$16e12ad1_at_ztechnt001>,   "John Claxton" <ztech_at_intrex.net> wrote:
> I have a text file which I have imported where many of the columns are
> duplicated. I would like to build a lookup table which references those
> columns.
>
> TABLE_1
> LASTNAME VARCHAR2(n)
> STREETNAME VARCHAR2(n)
> CITYNAME VARCHAR2(n)
>
> LASTNAME_T
> LASTNAME_ID NUMBER
> LASTNAME VARCHAR2(n)
>
> Each LASTNAME column in LASTNAME_T would have one (or more) matching
> columns in TABLE_1. I would like to build the LASTNAME_ID column with a
> sequence number.
>
> Something like this:
>
> INSERT INTO LASTNAME_T
> VALUES (seqkey.nextval, unique(TABLE_1.LASTNAME)
>
> Any ideas?
>

I'll use an extra temporary table here to point out the steps, but something like this will work:

• first get all the unique names --- CREATE TABLE table_temp AS SELECT DISTINCT LASTNAME FROM table_1 ;
• now put them where they belong, with unique ID numbers --- CREATE TABLE lastname_t AS SELECT lastname, rownum AS lastname_id FROM table_temp;
• clean up --- DROP TABLE table_temp; /

This could be done with a subquery, but I don't have ORACLE available on this machine to test it. And I have a habit of splitting these into separate steps because in more complex cases, it can be faster.

Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Apr 06 1999 - 08:55:51 CDT