× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
So the interactive 'delete from so...' and the imbedded are the same?
There isn't an override pointing SO to another file is there?

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 03:18 PM
Please respond to rpg400-l


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


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.

-- __--__--
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.





As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.