Re: HELP on INSERT into SOBCPLN

jmgurley_at_sun.cc.westga.edu (Jerry Gurley) wrote:

INSERT INTO SATURN.SOBCPLN (SOBCPLN_PIDM, SOBCPLN_TYPE_IND, SOBCPLN_TERM_CODE,

	SOBCPLN_SEQNO, SOBCPLN_CPLN_CODE, SOBCPLN_ACTIVE_IND, 
	SOBCPLN_ACTIVITY_DATE)
SELECT SPRIDEN_PIDM,'R', '199609',1,'REC1','Y',SYSDATE
	 FROM SATURN.SPRIDEN, SATURN.SRBRECR 
	 WHERE SRBRECR_PIDM = SPRIDEN_PIDM AND
	 SRBRECR_LEVL_CODE = 'UG' AND
	 SRBRECR_PIDM NOT IN (SELECT SOBCPLN_PIDM
	                     FROM SATURN.SOBCPLN)

Would have done it.
NOTE: no values clause when using SELECT to insert with. Just select out whatever constants you want.

>..... Looking for ideas........
 

> When faced with the problem that many students did not have a
>communication plan, we tried to insert into SOBCPLN using sqlplus. The
>code below is basically what we wanted to do, but the select statement
>returns multiple rows. According to the syntax, the query should only
>return one row, for the insertion. The goal was to insert a communication
>plan for all UG students who do not have one.
 

>INSERT INTO SATURN.SOBCPLN (SOBCPLN_PIDM, SOBCPLN_TYPE_IND, SOBCPLN_TERM_CODE,
> SOBCPLN_SEQNO, SOBCPLN_CPLN_CODE, SOBCPLN_ACTIVE_IND,
> SOBCPLN_ACTIVITY_DATE)
>VALUES ((SELECT SPRIDEN_PIDM
> FROM SATURN.SPRIDEN, SATURN.SRBRECR
> WHERE SRBRECR_PIDM = SPRIDEN_PIDM AND
> SRBRECR_LEVL_CODE = 'UG' AND
> SRBRECR_PIDM NOT IN (SELECT SOBCPLN_PIDM
> FROM SATURN.SOBCPLN
> )),
> 'R',
> '199609',
> 1,
> 'REC1',
> 'Y',
> SYSDATE
> )
>/
 

> Since this idea did not work, we created a script that creates
>sql code. This worked perfectly, but we are still interested in knowing
>if the same goal could be accomplished using the method above.
 

>spool insert.sql;
>select 'INSERT INTO SOBCPLN VALUES ('''||spriden_pidm||''',''R'',''199609'',1,''REC1'',''Y'',SYSDATE);'
> from saturn.SRBRECR,saturn.SPRIDEN
> where SRBRECR_PIDM = SPRIDEN_PIDM AND
> SRBRECR_LEVL_CODE = 'UG' AND
> SRBRECR_PIDM NOT IN (select SOBCPLN_PIDM
> from saturn.SOBCPLN);
>spool off;
>/
 

> Any ideas or solutions to this problem would be appreciated.....
 

>Jerry M. Gurley
>Computer Programmer I
>Computer Center Annex
>West Georgia College
>jmgurley_at_westga.edu

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Tue Oct 31 1995 - 00:00:00 CET