Re: Building a lookup table

John,

Your question got my curiosity, and here's what I came up with. It's designed for an initial load or an update to the lookup table.

INSERT	INTO lastname_t
SELECT	DISTINCT lastname
FROM	table_1 t1
WHERE	NOT EXISTS (
	SELECT	1
	FROM	lastname_t lt
	WHERE	lt.lastname = t1.lastname );

UPDATE	lastname_t
SET	lastname_id = seqkey.NEXTVAL
WHERE	lastname_id IS NULL;

COMMIT; NOTE: Assuming lastname_id represents a PRIMARY KEY column, you'll want to define the constraint as DEFERRABLE (you may also want a UNIQUE constraint on the lastname column in lastname_t). HOWEVER, there appears to be a bug in some versions of Oracle 8 :-(, as I got the following error when I went to COMMIT on my test against Oracle 8.0.5 on NT Server.

SQL> COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-01400: cannot insert NULL into ("RICH"."LASTNAME_T"."LASTNAME_ID")

Is there a better way to solve John's problem?

Is there a fix to the problem I stumbled into?

Cheers,
Rich
--
Rich Goldkamp, OCP (504) 523-5005 x2736 rjgoldka_at_dcss.com

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?
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Apr 05 1999 - 11:11:22 CDT