RE: Today's SQL Brain Teaser

["Jeffrey Day" <Jeff.Day@xxxxxxxx>]

..no offense meant, just curious... its a good tool in the toolbox -
know what I mean?

> > > On 11/10/2008 at 10:31 AM, in message
<C3254A3C48026444B8EEDAB75ED9704F01651C7503E9@xxxxxxxxxxxxxxxxxx>,
David FOXWELL <David.FOXWELL@xxxxxxxxx> wrote:
Because I told everyone I'd created a DSPFD!
Jonathan even went to the trouble of giving me the fieldnames so I
could just paste his request!

Now, why did I do a DSPFD when I had systables? Old bear and new
tricks.

There's another problem, I didn't make it clear that not ALL libraries
have a corresponding library called <PREFIX>FTEST. There are 5 or 6
<PREFIX>FTEST libraries each with a corresponding <PREFIX>F library.

So, inspiring myself with everyone's reply, I added an inner join
before the exception join like this ( takes ages to run, though )

with testfiles as (
SELECT substr(TABLE_SCHEMA, 1, length(trim(table_schema)) - 4  )
as library  , TABLE_NAME as testfile FROM systables
where table_schema like '%FTEST%' )

select distinct
       table_schema, table_name from systables a join testfiles t1
on a.table_schema = t1.library
exception join testfiles t2 on t2.library = a.table_schema and
a.table_name = t2.testfile

Thanks everyone!



-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Jeffrey Day
Envoyé : lundi 10 novembre 2008 15:26
À : Midrange Systems Technical Discussion
Objet : Re: Today's SQL Brain Teaser

Just out of curiousity, why didn't any of the people responding use
'systables'?  You remove the step of creating a work file with the file
names in it...

> > > On 11/10/2008 at 8:56 AM, in message
<fa303d750811100556g67efdaa0scfb89dbd2519f54a@xxxxxxxxxxxxxx>,
"Charles Wilt" <charles.wilt@xxxxxxxxx> wrote:
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.
--
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.



This message has been scanned for viruses by MailControl -
www.mailcontrol.com 

Click https://www.mailcontrol.com/sr/wQw0zmjPoHdJTZGyOCrrhg==  to
report this email as spam



If the reader of this email is not the intended recipient(s), please be
advised that any dissemination, distribution or copying of this
information is strictly prohibited. Johnson Matthey PLC has its main
place of business at 40-42 Hatton Garden, London (020 7269 8400).

Johnson Matthey Public Limited Company
Registered Office: 40-42 Hatton Garden, London EC1N 8EE Registered in
England No 33774

Whilst Johnson Matthey aims to keep its network free from viruses you
should note that we are unable to scan certain emails, particularly if
any part is encrypted or password-protected, and accordingly you are
strongly advised to check this email and any attachments for viruses.
The company shall NOT ACCEPT any liability with regard to computer
viruses transferred by way of email.

Please note that your communication may be monitored in accordance with
Johnson Matthey internal policy documentation.

This message has been scanned for viruses by MailControl -
www.mailcontrol.com 
--
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.