Hi, I'm new to FME. I need to query an SQL database to read records based on query with multiple joins and then write to CSV after some processing. Is a Reader the best for this or Can I use SQL creator to extract the records. Which is better option.

Hello @nisa​ , it will depend at which phase of the process you want to read the information in.

In the image below, I have used the featurereader as I put together the where clause based on other actions in the workbench.

I have found this approach very useful but have used the SQL Creator useful when just needing to read something from the database and stored for later use.

http://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Transformers/Transformers/sqlcreator.htm

Thanks @deanhowell​  As I understand with Feature Reader cannot perform table joins as in SQL. My workbench first queries an SQL Azure database using complex join and then uses the features generated for further processing. I'd like to confirm that the SQL Creator can do this.

You can use the SQLExecutor to preform SQL statements and receive the resulting rows as Features. I have used this a lot on a Postgres database with multiple joins and spatial selectors.

In this case I don't receive any features but let the database do all the work:

UPDATE maintable p SET owner = r.owner   FROM zones r WHERE owner IS NULL AND ST_CONTAINS(r.geom,ST_PointOnSurface(p.geom));

Also a SQLCreator can do this. In my opinion an SQLCreator is a Creator + SQLExecuter.

I don't have experience with SQL Azure databases yet but expect no issues here.