Welcome! Log In Create A New Profile

Advanced

Qry Design Problem

Posted by Michael Drechsel 
Forum List Message List New Topic
Michael Drechsel
Qry Design Problem
June 17, 2009 04:44PM
Hi,

I have to files in WD14:

File A:

Plan-Number, Date

File B:

Plan-Number, filename,filesize


The "plan-number" is the key for these 2 files.

The question is: Are there files in file B with file extension like ".pdf " ?

How can I create a qry for these question ? I tried something with a subquery and "is in the list .." but it does´nt work.

Because the File A has dozen of fields I would prefer the query editor ...

Any ideas ???
Reply Quote
Stefan Bentvelsen
Re: Qry Design Problem
June 17, 2009 04:59PM
Hi Michael,

have you tried something like this:

SELECT DISTINCT Plan_Number FROM B
WHERE filename LIKE '%.pdf'

for all Plan_numbers with files with .pdf in the file extension ?
Reply Quote
Fabrice Harari
Re: Qry Design Problem
June 17, 2009 05:02PM
Hi Michael...

considering that your query would mean testing on CONTAINS, which is the worse possible case (every record has to be read), I would suggest that you add an 'extension' field to your file with just the file extension in it... Then do the query on that field

Best regards

Reply Quote
Michael Drechsel
Re: Qry Design Problem
June 17, 2009 05:04PM
Hi Stefan,

thx, it´s clear, but I wan´t it do in one query with the editor. But I think its impossible.

Reply Quote
Paulo Oliveira
Re: Qry Design Problem
June 17, 2009 05:32PM
the query proposed by stefan is possible in the editor, i didn't test it in V14 but in V11 is possible.
In the editor select from the file A the Plan-Number, Date columns
Then select from file B Plan-Number, filename,filesize
In the file B Plan-Number define one condition (contains the value pdf)
If you need to remove duplicates, click the buttom Delete Duplicates

PS: This is true if in the analysis file A and file B are linked.
Reply Quote
Michael Drechsel
Re: Qry Design Problem
June 17, 2009 08:31PM
Hi Paulo,

it doesn´t work because:

If I link File A and B with a outer join with the plan_number as link and allow a record A without B (because not every plan_number in A has a filename in B ) the filter "filename contains ..." doesn´t work.

Reply Quote
Piet van Zanten
Re: Qry Design Problem
June 17, 2009 10:11PM
Hi Michael,

There's an option in the query editor to create calculated items.
Click the button on the left, then you can select Wlanguage functions.
Choose fExtractpath(fExtension) to create the calculated item.

Regards,
Piet
Reply Quote
Michael Drechsel
Re: Qry Design Problem
June 18, 2009 08:49AM
Hi Piet,

thx, but this is not my problem. See the post before.
Reply Quote
Piet van Zanten
Re: Qry Design Problem
June 18, 2009 09:31AM
Hi Michael,

As I understand you want to check the file extension.
If you create a calculated item, then you can create a selection condition (with parameter) on that item. To me that seems a clear solution. Or do I misunderstand the problem?

Regards,
Piet
Reply Quote
Michael Drechsel
Re: Qry Design Problem
June 18, 2009 09:36AM
The join doesn´t work.

But its ok, I try another solution with 2 querys.
Reply Quote
Al
Re: Qry Design Problem
June 18, 2009 11:48AM
Hello Michael

In the quest to solve the query issue, everyone seems to be overlooking Fabrice's suggestion, which in my opinion solves the entire problem very neatly. If you can adjust the database and add an "extension" field and index on it, then your query will be very simple and very quick. You can populate the new field from the existing data very easily with FExtractPath().

Regards
Al
Reply Quote
Michael Drechsel
Re: Qry Design Problem
June 18, 2009 11:56AM
Hi,

no.

If you join 2 files in the query editor and you allow show records from file A without records from file B (because not even record in file A has a record in File cool smiley the filter does´t work.

For example:

If no record contains "*.pdf" all records in File A are displayed !!

The file extension filter is used for my clients to seperate optional a set of displayed records.
Reply Quote
Newer Topic Older Topic
Author:

Your Email:


Subject:
Spam prevention:
Please, enter the code that you see below in the input field. This is for blocking bots that try to post this form automatically. If the code is hard to read, then just try to guess it right. If you enter the wrong code, a new image is created and you get another chance to enter it right.
Message: