Re: SQL --- DELETE FROM statement problem.

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

Rob Berendt asked;

In which step is file 'so' created?  And what is the SQL used to create
this file?

--__--__--


Rob;

The file 'SO' is one of the dds built production files that get purged.
(recs removed from it).

The 'spurgon' file (referred to in 'DELETE FROM' subselect) is created in my
program via sql as per a bit below;

...in english-ese...;
CREATE TABLE purgso/spurgon(
  ( killon     CHAR (  7  )  NOT NULL WITH DEFAULT )

...in rpg-ese...;


      *
      *****************************************************************
     C     @STEP2        BEGSR
      *****************************************************************
      * Create Table to hold 'Purgeable' Order Numbers...
      *
      *    Ready Create View / Join String...
      *
     C                   EVAL      String1  = 'CREATE TABLE'
     C                             + ' '
     C                             + onLib + '/' + onFil
     C                             + '('
     C                             + 'killon   CHAR (7 )'

     C                             + '  NOT NULL WITH DEFAULT'
     C                             + ')'
      *
     C                   EVAL      SqlStr = %TrimR(String1)
      *
      *    Prepare sql statement....
      *
     C/EXEC SQL
     C+    PREPARE SQLSTM2 FROM :SqlStr
     C/END-EXEC
      *
      *    Execute sql statement....
      *
     C/EXEC SQL
     C+    EXECUTE SQLSTM2
     C/END-EXEC
      *
      *
     C                   ENDSR
      *

John




( original message below  )
  ||                  ||
  \/                  \/
--__--__--

Message: 12
From: "Rusling, John B. (Alliance)" <jbrusling@alliancedev.com>
To: "'rpg400-l@midrange.com'" <rpg400-l@midrange.com>
Subject: SQL --- DELETE FROM statement problem.
Date: Wed, 1 May 2002 09:35:30 -0500
Reply-To: rpg400-l@midrange.com

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.

--__--__--