Hi all, 

 

 

I found this wonderful piece of informatjon in the description of ArcSDE reader (http://docs.safe.com/fme/html/FME_ReadersWriters/Default.htm#sde30/sde30.htm%3FTocPath%3DFME%20Readers%20and%20Writers%20(formats%20supported%20by%20FME%202014)%7CEsri%20ArcSDE%20Reader%2FWriter%7C_____0):

 

The SDE reader can also perform multi-table join queries, thereby exploiting the full power of the underlying RDBMS.

 

 

Cool... I wanna do that, and most likely either the ArcSDEQuerier or ArcSDE reader, whichever is most convenient. 

 

 

But I can't seem to wrap my head around the syntax. Composing a valid JOIN statement in SQL should be fairly straightforward, but that doesn't really seem to fit into the concept of WHERE Clause. Or I cant figure it out... 

 

 

Problem description: I have a huge ArcSDE database. I and I want to extract all data from table A, and a subset of information from table B, only extracting those whose ROUTE_ID values match ROUTE_ID in table A.  In my head, this should translate to a  very straightforward SQL:

 

SELECT * FROM A, B 

 

WHERE A.ROUTE_ID = B.ROUTE_ID

 

 

And FME ArcSDE reader takes care of anything up to and including the "WHERE", so i should input the  "A.ROUTE_ID = B.ROUTE_ID" - part where it says "WHERE Clause". 

 

 

Apparently not. What am I missing here? Or are there other, quicker ways to achieve what I want? 

 

 

I've also tested extracting table A and then separately get the data from table A that satisfy my conditions. Both the Joiner and FeatureReader are more than happy to do what I want - but way to sloooow, reading one. single. feature. per. database-query. Table A is big enough to make this unacceptable slow. (Although I could do it over the week-end, I guess). 

 

 

SQL executor / creator doesn't seem to support arcSDE, or at least I can't find that option. 

 

 

Other options I've considered:

 

 - Read A and B into, say, sqlite, then perform magic with the SQL executor.

 

 - Read A and B into workspace, then use FeatureMerger. Which will work, but slooooooooow. This is probably what I'll do if I can't make any progress on the SQL magic, letting the server do the work it's supposed to be really good at doing.