RE: SQL delete not in related table

You can concat more than two fields, i.e.:

VNDNR CONCAT WEEKX CONCAT DIGIT

Or use alternative syntax:  VNDNR || WEEKX || DIGIT

But, another thing to keep in mind is that this creates derivative fields
and query optimizer doesn't tend to use indexes when derivative fields are
involved.  So if you want your SQL statement's performance to scale to large
files, I strongly recommend going with Charles' option (NOT EXISTS).

Elvis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Friday, June 17, 2005 9:48 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL delete not in related table

> I don't think you can compare three fields like that.  Try 
> concatenating them all into one field in both the where and 
> the select.

Well I just learn more all the time.  The following worked:

DELETE FROM PODTL WHERE CONCAT(VNDNR, CONCAT(WEEKX, DIGIT)) 
          NOT IN SELECT CONCAT(VNDNR, CONCAT(WEEKX, DIGIT))      
          FROM POHDR);                                     

I discovered that you can only CONCAT 2 fields at a time.  It seems
interesting that as flexible and powerful is starting to appear to me, that
it would have such a restriction.  Guess RPG isn't the only language with
weird ones. <g>