Re: Question on Delete in Embedded SQL

Lori,

This can be done, and in ONE sql statement, providing we have some common
facts.  If one file is a parent of the other.  For example, if you have an
item file and an item activity file.  Let's say the key to ITEM is ITEM#
and the key to ITEMACT is ITEM# and UUID.  What you do is:
1)  Create a journal and start journalling on both files.
2)  Use Operations Navigator or some green command to set up referential
integrity between the two files.  When you set this up, tell it to CASCADE
upon delete.

Then when you delete the item# from ITEM it will delete any children from
ITEMACT.

Basically, in a good database design, steps 1 and 2 are already done,
right?

Rob Berendt

==================
Remember the Cole!


                                                                                
                                       
                    "Crane, Lori A."                                            
                                       
                    <LCrane@DoanePetC        To:     "'RPG400-L@midrange.com'" 
<RPG400-L@midrange.com>                 
                    are.com>                 cc:                                
                                       
                    Sent by:                 Subject:     Question on Delete in 
Embedded SQL                           
                    owner-rpg400-l@mi                                           
                                       
                    drange.com                                                  
                                       
                                                                                
                                       
                                                                                
                                       
                    04/03/01 08:41 AM                                           
                                       
                    Please respond to                                           
                                       
                    RPG400-L                                                    
                                       
                                                                                
                                       
                                                                                
                                       




Hello,

I have a question on delete in an embedded SQL.  Could anyone help me out.
Does anyone have an example of a working delete using SQL?  I am trying to
delete records out of two files with the same key.  My attempt is below.

Thanks in advance for any help
Lori Crane


7.00 **-*-*-*-*

8.00 **

4.00 **-*-*-*-*

5.00 c/exec SQL

6.00 c+ DECLARE C1 CURSOR FOR

7.00 c+  DELETE DOBPTP10 DOBPTP20

9.00 c+  WHERE order# = :DelORDER and

9.01 c+       dobptp10.ptagno = dobptp20.ptagno

2.00 c+  ORDER BY dobptp10.order# ,dobptp10.PTAGNO

3.00 c/end-exec

4.00 **-*-*-*-*

5.00

6.00 **-*-*-*-*

7.00 c/exec SQL

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---



+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---