How to properly compare 2 text fields in SQLExecutor

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.'

 

Check schema of the database table.

 

Perhaps the data type of GIS_ID field is "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 

 

Wait. I get it. All of them were inserts and no updates. Correct? Sorry I wasn't truly reading it.

I'll create a set that has only updates and test it

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.