Hi

 

You can use SQL on the fly with the inlineqierier.

 

See:

 

http://docs.safe.com/fme/html/FME_Transformers/Default.htm#Transformers/inlinequerier.htm

---

That's exactly what I need! Thanks.

---

Great to know btw you can use the IN operator also in the tester/testfilfer / attribute creator and also the fuzzy string comparer from the store can be useful

 

http://fmestore.safe.com/transformers/FuzzyStringComparer.htm

---

Hi,

 

 

If the two tables are contained in the same database, you can also create a joined table using the SQLCreator or the SQLExecutor.

 

"cross join" is the point. But the concrete SQL statements are different depending on the database system.

 

An example for MS-Access; assume table1 has ShortAddress field and table2 has LongAddress field.

 

-----

 

select t1.ShortAddress, t2.LongAddress

 

  from table1 as t1, table2 as t2

 

  where t2.LongAddress like (t1.ShortAddress & '*')

 

-----

 

 

Alternatively, a series of several transformers including FeatureMerger, ListSearcher and ListIndexcer could be used.

 

1) FeatureMerger (FME 2013 SP2 or later)

 

table1 --> REQUESTOR

 

table2 --> SUPPLIER

 

Join On: Requestor = 1, Supplier = 1 (the same constant to perform unconditional merging)

 

Process Duplicate Suppliers: Yes

 

Supplier List Name: _candidate

 

 

2) ListSearcher: Input MERGED features from the FeatureMerger.

 

List Attribute: _candidate{}.LongAddress

 

Search For: ^@Value(ShortAddress).*

 

Index Attribute: _list_index

 

Search Type: First regular expression match

 

 

3) ListIndexer: Input FOUND features from the ListSearcher

 

List Attribute: _candidate{}

 

List Index: _list_index

 

 

There is more than one way to skin a cat.

 

Takashi

---

In the SQLCreator or SQLExecutor, wildcard in the "LIKE" operator seems to have to be "%", even if the source format is MS-Access.

 

The SQLCreator with this statement works.

 

-----

 

select t1.*, t2.*

 

  from table1 as t1, table2 as t2

 

  where t2.LongAddress like (t1.ShortAddress + '%')

 

-----

---

Yes more then one way ot hurt cats...

 

 

u can create a merge-attribute using a creator wherin u

 

 

use a tcl string function.

 

Like>string compare str1 strg2] , 2string match str1 str2]

 

 

a range of usefull functions there.

 

 

I assume your tables are not in same order, else there would be no point to your question, you''l have to do full catresian product to find the matches(?)

 

---

I don't think you can use the tester here, though, can you? That is for comparing two fields in the same feature. What I want is an IN operator between two features.

---

I think the Tester can be also a way to skin a cat in a context like this:

 

FeatureMreger (unconditional merging) --> ListExploder --> Tester

 

Test Clauses: <full address>  |  Begins With  |  <short address>

 

 

"Like", "Matches Regex" or "Contains" could be a option depending on the situation. Won't hurt a real cat

---

You can use a tester.

 

 

But first u need to make all the combo's.

 

A cartisian product.

 

 

Like mr.Takashi said, unconditionally merge the tables.

 

Then u could build Str1 x Str2

 

(One way to do this i using a listbuilder using with a group by on Str1 and Str2. Then dump the duplicates, with a listduplicateremover.

 

Now what u are seeking is somewhere in this list.)

 

 

Or activate the list in the merger; again  what u are seeking is somewhere in the list.

 

 

Use something like

 

Like string compare str1 strg2] , astring match str1 str2]

 

or regular fme stringsearch transformers.

 

 

 

Basicaly samish as mr. Takashi suggestion.

---

i forgot...

 

 

It is all moot to Schroedingers cat....

---