Re: Today's SQL Brain Teaser

with tbl as (select atlib, atfile,
length(rtrim(atlib)) - 4) as matchLib
from dspfd
where atlib like '%FTEST'
)
select atlib, atfile
from dspfd A left exception join tbl B
on A.atlib = b.matchLib and A.atfile = B.atfile


This gives you files from <PREFIX>F that don't exist in <PREFIX>FTEST.

Do you want files in <PREFIX>FTEST that don't exist in <PREFIX>F?

HTH,
Charles

On Mon, Nov 10, 2008 at 4:10 AM, 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 libraries named LIKE '%FTEST%'


No prizes, just thanks for the best reply :


--
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.