Here's what I'm using:
select OBJECTID from TEST.LEASE_UPDATE_INSERT_WRITER where GIS_ID = '@Value(GIS_ID)'
Question
How to properly compare 2 text fields in SQLExecutor
I'm using a SQLExecutor that works great on comparing two text fields from 2 different ArcGIS layers on SDE. Basically it checks to see if a value is present in another layer. When the Text field has nothing but numbers it works great. But if they contain any text like "1909087a01" then it bombs out. It doesn't like it that it is not an integer even though it is a text field. Can I add something so it sees these as text comparisons. I'm sure the "=" is what's causing the issue, it doesn't care for "LIKE" either.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
Hi,
I think there is no problem in the SQL statement.
Did you get some error messages in the Log when the translation failed?
Takashi
Hi,
I suppose the field GIS_ID is a text field and not a numerical field, right?
You could try to make a slight modification to your WHERE-clause:
... where upper(GIS_ID) = upper('@Value(GIS_ID)')
and see if that works better. If you still get errors, please post the error messages here.
David
Here's the original error:
MS SQL Server (Spatial) Reader: Query failed, possibly due to a malformed statement.
Query Text `select OBJECTID from TEST.LEASE_UPDATE_INSERT_WRITER where GIS_ID = 01902938'.
Provider error `(-2147217913) Conversion failed when converting the nvarchar value '0190a938' to data type int.'
Takashi ... They are both TEXT fields. SQL doesn't like the "=" or LIKE usage. From what I've read online even if it is a text field it can still compare values that look like numbers. It just can't do that if the values contain a letter.
Would you 2 know of a possible OTHER way to check if a value exists? There might be other ways that do not use the SQLExecutor. Here's what I sent to support@safe.com in an email that explains exactly what I'm trying to do (Candace is a Safe employee) :
Candace,
I wanted to add more information.
Reader = TEST.LEASE
Writer = EDIT.LEASE
I don’t think this is an FME issue. I believe it's a limitation of SQL when used in the SQLExecutor. Let's say I have a layer called TEST.LEASE and I want to compare it to a layer called EDIT.LEASE based on one unique ID field. Both of these layers are on SDE. We use SQL Server for our SDE data. There is a TEXT field in both layers called GIS_ID. This is a unique ID field. So what happens is we get updates on our LEASE layer and they start off being loaded to TEST.LEASE. When we have done our QA/QC of the data and we are satisfied that they are ready to be uploaded to EDIT.LEASE we then run an FME job that serves as our promotion tool. What this promotion tool does is that it checks various fields in TEST.LEASE to make sure they qualify for being uploaded (this part works 100% without issue).
Right before they are promoted to EDIT.LEASE we need to know if this will be a completely new record, in which case we will do an INSERT with FME. If by chance the GIS_ID already exists then we need to do an UPDATE to those records. The tool we have works perfectly for determining if it is an INSERT or UPDATE, except for one seemingly small thing … IT ONLY WORKS IF THE TEXT FIELD CONTAINS A NUMBER THAT DOESN’T HAVE A LETTER IN IT.
As mentioned in my previous emails. The GIS_ID is a text field (in both layers and they are both the same size, there is no difference in the field in both layers). As you may know, SQL doesn't care if it is a TEXT field or an INTEGER field when all that is contained in that field is a number. It can still compare 202 to 202 to see if they are equal to each other. For my example let's say I have a record in both TEST.LEASE and EDIT.LEASE where both of their GIS_ID fields equal 09198760. When I run the query I sent early (which I will also include here):
select OBJECTID from TEST.LEASE_UPDATE_INSERT_WRITER where GIS_ID = @Value(GIS_ID)
It runs perfectly, as I’ve mentioned, on the data if both GIS_ID text fields have only numbers in them. But if just one record contains an actual alpha, the SQL query will error out.
So if GIS_ID has 09198760a01 once the query reaches the “a” in GIS_ID the FME job stops right there. I’m not looking for a way for the job to continue and ignore those records, because I need ALL OF THE RECORDS to load. I need to know if anyone at Safe would know how to add to or rewrite the query above so that it loads both of the "number only text" fields and "numbers containing a letter" fields.
I hope that long explanation is clear. Please let me know if it isn’t. I will talk with my manager to see if I would be allowed to upload our data to you so you can have all of the pieces of this puzzle. Some people have gotten in trouble in the past for not getting it approved first.
Thanks for any help you might be able to provided for me
Hi,
looking at the error messages, it seems that you're missing the string quotations around your attribute value:
GIS_ID = 01902938
should read
GIS_ID = '01902938'
Otherwise SQLServer will interpret 01902938 as an integer before doing the comparison. Verify your SQL statement.
David
David would I place those quotes around the Value(GIS_ID) portion? Like '@Value(GIS_ID)' or @Value('GIS_ID')
Exactly the same way as I wrote it out further up in this thread: '@Value(GIS_ID)'
You can also consider posting a screenshot of your SQLExecutor code window here.
David
David. It went all the way through that time and says the translation was successful. But I did notice these again for all of the records. I've only included a few of them below. Do you know what this means? Does it really affect the output?
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4250
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4251
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4252
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4253
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4257
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4258
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4259
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4260
No rows matched UPDATE query to table/feature class 'TEST.LEASE_UPDATE_INSERT_WRITER' where OBJECTID=4261
No rows matched UPDATE query to table/feature class
Hi,
if you look at the two screendumps above, you'll clearly see the problem:
Dump 1:
Dump 2:
See how dump 1 is missing them?
David
Sorry my bad on that. I took screen dump 1 before I added the suggested quotes you see in screen dump 2.
Login to the community
No account yet? Create an account
An FME Account is required to contribute
LoginEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.