RE: SQL Ideas Please

Off the cuff and ugly, but you could try a

Delete from file where SCACCODE concat TRLRNUM concat ASNNUM concat ORDERTYP
concat ORDERNUM not in (select distinct(SCACCODE concat TRLRNUM concat
ASNNUM concat ORDERTYP concat ORDERNUM) from file)

If the fields are numeric, you'll want to do char(trlrnum) for example. Big
and slow. Remember to test first!!

HTH,
Loyd

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713
-----Original Message-----
From: Brian Piotrowski [mailto:bpiotrowski@xxxxxxxxxxxxxxx] 
Sent: Tuesday, May 16, 2006 13:21
To: Midrange Systems Technical Discussion
Subject: SQL Ideas Please

 

Can someone tell me if there is an easy way to delete records from a
table where there are only four data elements on which I wish to base
the delete routine?  For example, I have a table that has numerous
records in it.  I want to delete all records other than those that have
a distinct SCACCODE, TRLRNUM, ASNNUM, ORDERTYP and ORDERNUM.  However,
I'm not sure how to express this in SQL, but in pseudo-code, I'd like to
do this:

 

DELETE ALL RECORDS IN SSW85 EXECPT THOSE RECORDS THAT HAVE A DISTINCT
(SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM).