RE: SQL delete not in related table

Jeff,

Use CONCAT this way:

        vndnr CONCAT weekx CONCAT digit

HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeff Crosby
> Sent: Friday, June 17, 2005 10: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>
> 
> -- 
> Jeff Crosby
> Dilgard Frozen Foods, Inc.
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
> 
> The opinions expressed are my own and not necessarily the 
> opinion of my
> company.  Unless I say so.
> 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>