RE: Question on Delete in Embedded SQL

Lori,

I think you will need to split this into two (or possibly more) statements.
I don't think you are allowed to delete joined files. Is your criteria to
delete based on order number only? If so, why the relationship on "ptagno"?
Are you only deleting records that "ptagno" is in both files, or is the
objective to get rid of the entire order? 

Assuming you are removing every record of an order, you would use two SQL
statements:
c/exec SQL
c+  DELETE DOBPTP10 
c+  WHERE order# = :DelORDER 
c/end-exec

c/exec SQL
c+  DELETE DOBPTP20 
c+  WHERE order# = :DelORDER 
c/end-exec

You don't need to specify an order by since DELETE processes the entire
result set; record order in the result set is irrelevant. Now, this gets
more complicated if you need to delete only some of the records in the
order. Your example seems to indicate this with the comparison of "ptagno"
in the two files. If your intention is to delete only the records that are
duplicates in the two files (that is, ptagno is the same in both files), you
would need some way identify the ptagno records before you delete from the
two files. If that's what you need, let me know and I'll work up an example
of that.

hth,
Eric DeLong


-----Original Message-----
From: Crane, Lori A. [mailto:LCrane@DoanePetCare.com]
Sent: Tuesday, April 03, 2001 8:42 AM
To: 'RPG400-L@midrange.com'
Subject: Question on Delete in Embedded SQL


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