using sequence in insert w/ select

From: sweth+oracle-misc_at_sweth.gwu.edu <(sweth+oracle-misc_at_sweth.gwu.edu)>
Date: Fri, 23 Apr 1999 16:59:23 GMT
Message-ID: <LF1U2.213$Ao3.3309@fozzy.nit.gwu.edu>

        i am attempting to run a sql script that will seed a table with values; the table to be seeded, however, has required created_by and created_date fields, the former of which is a foreign key to a user table. i thus need to do an insert with select from the script, to find out the person_id from the user table that corresponds to myself, since i will be running this script in multiple instances, and my person_id will vary from instance to instance.

        the problem i am running into, however, is that the table to be seeded also has a primary key that i am populating with a sequence; when i try to reference sequence.nextval in my insert with select, i get an error saying that a sequence number is not allowed in that location.

        here is a sample of the sort of statement i am trying to execute:

insert into entity_role_info (

   ENTITY_ROLE_ID
,ENTITY_TYPE
,ENTITY_TITLE
,ORG_UNIT
,ROLE_SORT_ORDER
,BUILDING
,PERSON_ID
,SUITE
,CREATED_BY
,CREATED_DATE

) select DirectoryIDSequence.nextval

      ,'PERSON'
      ,'IS Coordinator'
      ,'Technology Services'
      ,'1'
      ,'Academic Center'
      ,min(person_id)
      ,'#B205'
      ,min(person_id)
      ,sysdate

   from person_info
   where UPPER(first_name) = 'SWETH'

      and UPPER(last_name) = 'CHANDRAMOULI' ;

        if i change this to a straight "insert into table with values" style statement, and hard-code in the person_id, everything works fine. what am i missing here?

	tia for any advice,
	sweth.

--
Sweth Chandramouli
IS Coordinator, The George Washington University <sweth_at_gwu.edu> / (202) 994 - 8521 (V) / (202) 994 - 0458 (F) <a href="http://astaroth.nit.gwu.edu/~sweth/disc.html">*</a> Received on Fri Apr 23 1999 - 11:59:23 CDT