|
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 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.
