RE: SQL Ideas Please

Elvis,

Is the with...delete syntax new to V5R3 or R4? This does not work on my V5R2
system.

Thanks,
Loyd

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713

-----Original Message-----
From: Elvis Budimlic [mailto:ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx] 
Sent: Tuesday, May 16, 2006 14:23
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Ideas Please

Brian, I'm not sure I fully understood you (especially DISTINCT part - SQL
keyword?) so make sure you test this on a dummy/copied file first.  
Alternatively, just run the SELECT part on its own to verify that projected
rows are ones you want to preserve.

WITH preserve AS (
  SELECT SCACCODE,TRLRNUM,ASNNUM,ORDERTYP,ORDERNUM,MIN(FDATE) AS FDATE 
  FROM SSW85
  GROUP BY SCACCODE, TRLRNUM, ASNNUM, ORDERTYP, ORDERNUM)
DELETE FROM SSW85 A, preserve P
WHERE A.SCACODE  = P.SCACODE  AND 
      A.TRLRNUM  = P.TRLRNUM  AND 
      A.ASNNUM   = P.ASNNUM   AND
      A.ORDERTYP = P.ORDERTYP AND
      A.ORDERNUM = P.ORDERNUM AND 
      A.FDATE   <> P.FDATE

Elvis