FME Querying an Microsoft SQL Server Non-Spatial to perform an attribute join

Question

I am trying to join a spatial table with some values coming from a MS SQL table.

I have it working properly with an excel but when I try to use SQLExecutor or SQLCreator I get stuck where the DatabaseJoiner only allows me to choose SQL tables and not the result of the SQLCreator.

enter image description here

enter image description here

But in the DBJoiner I can't get the reader to be the result from the SQL enter image description here

Do I have to write to a csv/excel and then read use this for the db joiner? it's best to avoid the creation of files even if temporary.

UPDATE: I need to run the query to get the fields I need to join from the MSSQL -they aren't in a table but a result of a query.

Basically the process is. 1. Get current cadastre and clean it to be in the format we require (done as part of FME Merge two files that only have some attributes the same) 2. Perform 3 sets of queries from the db that *a -match lot plan to the council's land_no and get some address fields, *b -use the land_no and get a property no from a second query, *c -use the property number to get the owner info and put this into the table.

3- the queries seem to work fine but when I use DB Joiner of Feature Joiner it's overwriting the fields that were previously populated

Answer 1

You should just be able to use the DatabaseJoiner transformer to join with a SQL database; just as you do with Excel.

Is there a reason you have the SQLExecutor/Creator there? If it's to do some sort of pre-query of the data (which I don't honestly think should be necessary) then use a FeatureJoiner instead of the DatabaseJoiner.

Basically:

• FeatureJoiner has two input ports for when both sets of data to join are already being read into the workspace.
• DatabaseJoiner has a single input port for when one set of data is in the workspace and the other set exists in a database elsewhere.

Or check out the Prefetch Query setting in the DatabaseJoiner, which might be another way to avoid the SQLCreator.

• GeorgeC: I've been trying this but it doesn't work. I'll update the question.