Re: Today's SQL Brain Teaser

Hi David

How about:

select atfile, atlib, atftyp, atfila, attxt
from filelist a
where trim(atlib) like '%F'
and (select count(*)
from filelist b
where a.atfile = b.atfile
and concat(trim(a.atlib), 'TEST') = b.atlib
and a.atftyp = b.atftyp
and a.atfila = b.atfila) = 0

All the best

Jonathan



David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote :

Hi all,

I have several libraries named <PREFIX>F each having a duplicate

library named <PREFIX>FTEST. All files in library <PREFIX>F SHOULD
have a file with the same name in <PREFIX>FTEST. However, there are
imperfections and now many files don't exist in the library

<PREFIX>FTEST. I have a file named DSPFD resulting from DSPFD


*ALLUSR/*ALL to work with. I want to list the missing files from the


No prizes, just thanks for the best reply :


--
libraries named LIKE '%FTEST%' This is the Midrange Systems Technical
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.

Jonathan Mason
iSeries Consultant
www.astradyne-uk.com


_______________________________________________________
This message was sent using NOCC v1.14 webmail software
_______________________________________________________