Re: SQL --- DELETE FROM statement problem. -- RPG400-L

Rob,

Well, it's an orders header vanilla sequential file, with logicals built
over it.  One logical for read, one logical (unique) for update, and some
others.

SO; the physical file source lists only fields, nothing hidden there.

SOR; the logical read looks like this
A*=
A***************************************************************
A                                      REFACCPTH(SOU)
A          R FM$SO                     PFILE(SO)
A                                      FORMAT(SOU)
************* End of data ***************************************

SOU; the logical update looks like this
A***************************************************************
A                                      UNIQUE
A          R FM$SO                     TEXT('Sales Order Header')
A                                      PFILE(SO)
A          K SOON
************* End of data ***************************************


I can run...

DELETE FROM so
  WHERE soon IN
    (SELECT killon
       FROM purgso/spurgon)
  WITH NC
129 rows deleted

...using interactive sql and it works.??

AND I can run....

DELETE FROM sor
  WHERE soon IN
    (SELECT killon
       FROM purgso/spurgon)
  WITH NC
129 rows deleted

...using interactive sql and it works.??


John





-----Original Message-----
From: rpg400-l-request@midrange.com
[mailto:rpg400-l-request@midrange.com]
Sent: Wednesday, May 01, 2002 1:51 PM
To: rpg400-l@midrange.com
Subject: RPG400-L digest, Vol 1 #705 - 8 msgs


Send RPG400-L mailing list submissions to
        rpg400-l@midrange.com

To subscribe or unsubscribe via the World Wide Web, visit
        http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
        rpg400-l-request@midrange.com

   8. Re: SQL --- DELETE FROM statement problem. (rob@dekko.com)



--__--__--

Message: 8
To: rpg400-l@midrange.com
Subject: Re: SQL --- DELETE FROM statement problem.
From: rob@dekko.com
Date: Wed, 1 May 2002 13:51:50 -0500
Reply-To: rpg400-l@midrange.com

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Can you post the DDS for the 'so' file?

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"Rusling, John B. (Alliance)" <jbrusling@alliancedev.com>
Sent by: rpg400-l-admin@midrange.com
05/01/2002 12:30 PM
Please respond to rpg400-l


        To:     "'rpg400-l@midrange.com'" <rpg400-l@midrange.com>
        cc:     "'rob@dekko.com'" <rob@dekko.com>
        Fax to:
        Subject:        Re: SQL --- DELETE FROM statement problem.


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.

-- __--__--