RE: SQL Deletes

["Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>]

I would use commitment control method.
1) Simply rollback when encounter error.  Commit when no error. 
2) This method keep the file clean.  No deleted record. 
3) Run much faster.  File under commitment control run faster and there is no 
need to go back to find those records that need to be deleted.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Elvis Budimlic
Sent: Tuesday, September 13, 2005 10:22 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Deletes


Can you use commitment control and then simply rollback the uncommitted
changes when the error condition is reached? That seems like the 'right'
solution to me.
Or perhaps defining cascade delete constraints so when you delete from one
file, appropriate records are automatically deleted from the other files?

But, if you want to use SQL, something to the effect of:

DELETE FROM MYFILE1 
WHERE field1 = document_number AND 
      field2 = 'date'          AND
      field3 = 'year'

Elvis

-----Original Message-----
 Subject: SQL Deletes

Good morning from a slightly damp Midwest!

I have a question regarding multiple deletes in a RPG
/Free program I am writing. I have a status field
that, at anytime during the parsing of an XML "sheet",
can be set to 'F' to fail a document (through errors
like letters where there are supposed to be numbers or
blanks for required information). 

The program ultimately updates/writes to 7 different
db files, BUT it writes as it goes (I chose to reuse
certain fields rather than trying to guess how many
unique fields I would need since using arrays was
poo-pooed by the team leads). So if file 4 fails, then
there are fragments in 1,2 and 3 that I need to get
rid of. I think an SQL delete would be faster to write
than a set of repeated CHAIN deletes. 

How would I pass in the parameters of document number,
date and year? I honestly havent messed much with SQL
since school (and that was only on a faked up Oracle
db for Oracle class).

Thanks for any advice in advance!
JenR