I am trying to read in the SchemaMapper from a Microsoft SQL Server Non-Spatial table based on a where clause. The value in the where clause depends on the value from another table. So I first read that other table and create an attribute with that value. In the SchemaMapper the configuration is as follows:
You can already notice that there are no options to assign an attribute value or open a text editor for the WHERE Clause field.
When I run the workspace I get the following error:
Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `SELECT * FROM DataOwner.[RAC_SCHEMA_DEFINITION] WHERE MAPPING_ID = '@Value(MAPPING_ID)''. Provider error `(-2147217900) Conversion failed when converting from a character string to uniqueidentifier.'
A fatal error has occurred. Check the logfile above for details
SchemaMapper_Mapper(SchemaMappingFactory): Could not read feature: A fatal error has occurred. Check the logfile above for details
I appears that it is not possible to assign an attribute value inside the where clause. It just inserts the @Value(...) as text. When I try this inside a FeatureReader for Microsoft SQL Server Non-Spatial it is possible to assign an attribute value or open a text editor and running it works like a charm.
Is this a bug or non-existent functionality? Or is there a reason that this is not possible?
I am already thinking of a workaround.
Option is using a parameter.
Populating the parameter with an @Value(...) will give the same result as the above issue.
It appears to work with a pre-defined string as input parameter and using $() inside the SchemaMapper where clause. Problem is that the value is than not dynamic anymore.
I could look into catching the wanted value with Python inside the parameter, but I would like to minimize Python/arcpy.
Other option is adding (a) row(s) to the input data for the SchemaMapper and using a SchemaMapper filter instead of a where clause. This could work, but input data can have an amount of rows order of millions of rows, so I would like to avoid adding an attribute to that data. Also the mapping table for the SchemaMapper could have many rows that I won't need and I would like to avoid processing these.
Any other options? Or explanation to my earlier question?
