Skip to main content
  • EN

I'm looking for some help to use SQL in 'where clauses' for FME Readers.

I'm trying to read from an ArcSDE database, using SQL operations like GROUP BY or DISTINCT. But I am under the impression that ArcSDE is not compatible with more complex statements than the simple terms like: equals/is null/not null/ in range / and so on.

 

On the documentation page I read however: "Enter any SQL where clause that constrains the attributes of the layers selected in the layer list."

 

I have found the SQLCreator and SQLExecutor transformers, however these do not support ArcSDE format.

 

I'm starting to think that the ArcSDE format simply does not suppport 'advanced' SQL querying in FME. Is this correct?

So far I have found 1 alternative: a PythonCaller using these ArcSDESQLExecute functions from ArcPy.

 

Could someone tell me if this is indeed the only workaround? Are there other workarounds? I've not found any further documentation on this topic, perhaps someone else has some useful references for me?

@takanaka​ Geodatabase has been added to the list of formats supported by SQLCreator/Executor for FME 2023 - due for release in early April. You can test the functionality in the FME 2023 betas if you wish. There are very likely nuances on what SQL is supported based on the underlying database etc. FME doesn't use ArcSDESqlExecute functions. I think you could probably use this as a guide to what SQL is supported.

You can use a FeatureReader and construct a where clause using things like GROUP BY and DISTINCT, since a FeatureReader in this situation is just doing a select * from table.

Something like this:

OBJECTID = (SELECT MAX(OBJECTID) FROM MASTER.TABLE)

OBJECTID in (SELECT MAX(OBJECTID) FROM MASTER.TABLE GROUP BY Other_Field)

is a way to use aggregate functions like GROUP BY

 

You can even add in SQL spatial to test for overlaps with input areas

SecondaryID in

(SELECT SecondaryID

FROM MASTER.TABLE_evw

WHERE PropertyID = @Value(PropertyID)

AND SHAPE.STIntersection(0x@Value(_geometry)).STArea() > 0.9

)

Reply


Community Stats

30,126
Posts
114,353
Replies
37,759
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.