How to properly compare 2 text fields in SQLExecutor

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

Check schema of the database table.

 

Perhaps the data type of GIS_ID field is "int"?

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

David would I place those quotes around the Value(GIS_ID) portion? Like '@Value(GIS_ID)' or @Value('GIS_ID')

 

 

 

Wait. I get it. All of them were inserts and no updates. Correct? Sorry I wasn't truly reading 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