Re: help with complex insert inside a procedure

From: Torrey Maggard <torrey_maggard_at_yahoo.com>
Date: 5 Feb 2002 14:20:20 -0800
Message-ID: <fcc40fe2.0202051420.71b1858b@posting.google.com>

Chris, I believe that a change in your syntax for your insert will allow you to do your insert. I think that you will need to play with the Select statements so that all values can be queried using 1 SELECT statement instead of multiple select statements. For each row being inserted, the select statements should return exactly one value, so you may be able to list all tables in your FROM clause without tieing them together since the cartesion join should be 1 record total per insert. The following syntax could then be used...(*Note - do NOT use the VALUES keyword.)

INSERT INTO chris_b.SWT_DIALOG_ACTIONS

  (SDA_UID_PK, SDA_SWT_CMDS_UID_FK,
   SDA_SWT_DIAL_QUESTIONS_UID_FK, 
   SDA_SWT_DIAL_RESPONSES_UID_FK,
   SDA_ACTION_FL,
   SDA_ACTIVE_FL,
   SDA_QUES_RESP_SEQ_NO, 

   CREATED_BY,
   CREATED_DATE
  )

 select sda_seq.nextval, swc_uid_pk, ...
 FROM   swt_cmds, ...
 WHERE  SWC_SWT_EQUIPMENT_UID_FK =  (select seq_uid_pk 
                                     from   swt_equipment  
                                     where  seq_code =  'CVX'...)

.
.
.

;

Best of luck! Received on Tue Feb 05 2002 - 16:20:20 CST