Question

Problem with where clause in InlineQuerier

  • 17 January 2018
  • 4 replies
  • 7 views
A

Hi All,

I have added two list as input tables to InlineQuerier and have problem with where clause in it.

In Outputs if I create a select query base on one table it works, but I want to use where clause in that query, and after adding any sort of where clause even easy one like (where field = "value"), it is not returning anything.

I want to find records in one list which are not in the other list, with query like this:

SELECT "name","type","fme_feature_content" from Table1

Where "name" in (Select "name" From Table2)

Appreciate for any help.

 

 

4 replies

takashi
Rank
Userlevel 2
Badge +17
What happens when you run the workspace? Does FME log error messages?

 

 

erik_jan
Rank
Userlevel 2
Badge +16

This could also be solved using the ChangeDetector or the FeatureMerger transformers.

scyphers
Rank
Badge +5

Are you getting errors in log window about invalid name If the field "name" exists in both tables you might need to qualify the tablespace. FME might not return a result when the namespace is unclear.

Try something like >

SELECT t1.name, t1.type, t1.fme_feature_content

from Table1 t1, Table2 t2

where t1.name in (select distinct t2.name)

order by t1.type

****Update

Oooops.... I just spotted you want recodset NOT in the the other table. Try

SELECT * from Table1 t1

where not exists

( select 1 from Table2 t2

where t1.name = t2.name)

that might chase them out. ;)

G
Badge +3

Did you add the attributes to the inline query data sets to read.

This happens if you add datasets and then later change or add a attribute not in the sets. (alas not dynamic...)

Check in the columns of the tables if the attributes are there.

The querier does not do this checkin for you, it will let you go ahead until at runtime it reports an error.

Also I assume you did check the letter cases? (had to ask)

Also I found in (2016 version) attribute names with spaces to be an issue for the ILQ. I replace them with an underscore.

Reply


Community Stats

28,721
Posts
109,501
Replies
36,560
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.