Question

How to filter one alphanum table with a text file ?

L
Badge
Hi,

 

 

My workspace has as input an alphanumeric table from an Oracle database.

 

 

I'm trying to introduce a text file (for easy editing) to filter the above Oracle table, matching a complete text file line with an attribute field in the table.

 

 

How can I do this ?

 

 

I've tried to utilize the "FeatureReader", but cannot seem to get it to perform the matching. I.e. all "_matched_records" are zero, even though I've added an existing value from the source table field to the text file. Both the "<Other>" and "text_line" ports in the FeatureReader yield no output.

 

 

Cheers.

5 replies

takashi
Rank
Userlevel 2
Badge +17
Hi,

 

 

There should be several ways. If the Oracle database is non-spatial, I would use the Text File reader and the SQLExecutor transformer.

 

Text File Reader --> SQLExecutor --> result

 

SQL Statement example:

 

select * from table_name where field_name = '@Value(text_line_data)'

 

 

Takashi
L
Badge
Hi Takashi,

 

 

Thanks for your answer. Unfortunately, I don't think it'll suit my need.

 

 

When I say "filter", I mean "remove". I.e. I want all records in the Oracle table, that _doesn't_ appear in any of the lines in the text file.

 

 

I think the SQLExecutor as provided will require a 1-to-many relationship, not a many-to-many, am I right ?

 

 

But thanks for reminding me that the problem isn't necessarily "oracle-with-textfile-addon", but could be "textfile-with-Oracle-addon" as well. One can easily aquire a blind spot out of frustration :-)

 

 

Cheers

 

Lars
david_r
Rank
Userlevel 4
Hi,

 

 

one solution could be to read both your Oracle table and your text file. Pass them into a FeatureMerger, with the Oracle table on the Requestor and the text file as the Supplier.

 

 

The features that exit NotMerged will be those from the Oracle table that aren't referenced in your text file.

 

 

David
takashi
Rank
Userlevel 2
Badge +17
The FeatureMerger would be a steady solution.

 

 

SQL is flexible. In this case, I think "not in" operator can be used in the statement after some pre-processing.

 

Text File Reader

 

--> StringConcatenator (surround each text line by single quotations)

 

--> Aggregator (concatenate all text lines by comma separater)

 

--> SQLExecutor

 

-----

 

select * from table_name where field_name not in (@Value(concatenated))

 

-----

 

If the field is a numeric type, the quotations (StringConcatenator) is not necessary.

 

L
Badge
Thanks David,

 

 

I'll have a look at the FeatureMerger.

 

 

Thanks Takashi,

 

 

I'm familiar with this approach, and will check it out.

 

 

Cheers

Reply


Community Stats

28,661
Posts
109,318
Replies
36,528
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.