SQL --- DELETE FROM statement problem.

["Rusling, John B. (Alliance)" <jbrusling@xxxxxxxxxxxxxxx>]

Help with sql DELETE FROM statement.

Here's the process...

There are files to purged based on some criteria.

I create a table with the order numbers that meet that criteria.

By purge I mean, copy them into a 'purge' library first then
 delete them from the production library files.

This is all done in 1 rpgle program.


1st step is ----;
Create a view using Orders File & OrdersStatus File information.
 (..rec selects for purgeable records criteria here...(sql)
This gets me the records eligible to be purged.


2nd step is ----;
Create a table (using sql) to hold Order#'s for purge.
  (...will be used later to copy / then purge records...)


3rd step is ----;
Populate the table with eligible order numbers.
 (using the view from step 1. sql...)


4th step is ----;
Delete the view in step 1.(qcapcmd stuff)


5th step is ----;
Run some crtlib, cpyfiles to have a home for the purged records.
 (qcapcmd stuff)


6th step is ----;
Copy the records to be purged into their new 'home'.

 (sql insert into step5 created shell files...)


7th step is ----;
Delete the records to be purged into their new 'home'.

 (sql DELETE FROM...)



Everything works except step 7 (lucky seven!)



My step 7 sql statement is ---;

      *
      *    Delete purge/history records from production files...
      *
     C                   EVAL      String7  = 'DELETE FROM so WHERE soon IN'

     C                             + ' (SELECT killon FROM purgso/spurgon)
     C                             + ' WITH NC'
      *
     C                   EVAL      SqlStr = %TrimR(String7)
      *
      *    Prepare sql statement....
      *
     C/EXEC SQL
     C+    PREPARE SQLSTM7 FROM :SqlStr
     C/END-EXEC
      *
      *    Execute sql statement....
      *
     C/EXEC SQL
     C+    EXECUTE SQLSTM7
     C/END-EXEC
      *

     (** I omitted the filename / fieldname substitutiosn for clarity)





The message I get is---:


                        Additional Message Information



Message ID . . . . . . :   SQL0150

Date sent  . . . . . . :   04/30/02      Time sent  . . . . . . :   15:06:34



Message . . . . :   View or logical file SO in JBRUSLING read-only.



Cause . . . . . :   Update, delete, or insert is not allowed. SO in library

  JBRUSLING can be used only for read operations.

    A view or logical file can be used only for read operations if one or
more
  of the following conditions are true:



     -- The view contains a DISTINCT keyword, GROUP BY clause, HAVING
clause,
  or a column function in the outer-most subselect.



     -- The view or logical file contains a join function.



     -- The view contains a subquery that refers to the same table as the

  table of the outer-most subselect. A view of this type may be used for

  inserting rows.



     -- All the columns of the view are expressions, scalar functions,

  constants, or special registers.



     -- All the columns of the logical file are input only.



     -- The select list of the view omits a column of the based on table
that
  does not allow null values or default values.  Inserting into the view is

  not allowed.



I can't quite see where any of these affect me unless it thinks the step 1
 view is still in existenece for act group.



Any Help Is Welcomed.  Thank You.

John B.