× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.