Re: Today's SQL Brain Teaser

["Jonathan Mason " <jonathan.mason@xxxxxxxxxxxxxxxx>]

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
_______________________________________________________