Make Query Table - Field conversion

[ChrisMatthews]

Hello

I'm creating a model that is sucking in data from an Oracle database, which I intend to turn into an Event Layer.

Usually this is pretty simple, however on this occation the X and Y coordinates have been stored as strings.

Is it possible to convert field data types when bringing in the data with the Make Query Table tool. Clearly if I was using straight SQL to the database I could do that)?


All help and suggestions appreciated.

Regards

Chris

[JoshuaChisholm]

Are you trying to make points with the X and Y fields? Or are they simple to be used as a data field for your records?

You could use the Add Field tool to make two numeric X and Y fields and then use the Calculate field tool to convert the string field to a number. You could probably skip the add field tool if you are confident that all the records will convert without error.

Your expression could be something like:

float( !XField!)

Let me know if that helps at all.

[JamesCrandall]

Fix the database, it's that important.  I just see no value in doing gymnastics to wrestle things that should be modeled for what they are at the database tier.

If it's a date, store it as such.  Decimals, floats, integers?  Then that's what they are period. 

Get with the database admin, and make it right.  You will save yourself tons of headaches, not just in the immediate, but long-term life-cycles of the tools and applications you build will be better off.

[ChrisMatthews]

Yes I'm going to use table to create an event layer, which is why I'm wanting to convert the field type.

I've tried using the Add Field Tool, but it throws an error on a table created with the Make Query Table tool. Although it wouldn't be my first choice to solve this problem, I ought to be possible to convert on the fly 😞

I'll try your suggestion of converting within the Make XT Event Layer and see what happens. I'll report back here with the results.

[ChrisMatthews]

Fix the database, it's that important.  I just see no value in doing gymnastics to wrestle things that should be modeled for what they are at the database tier.

If it's a date, store it as such.  Decimals, floats, integers?  Then that's what they are period. 

Get with the database admin, and make it right.  You will save yourself tons of headaches, not just in the immediate, but long-term life-cycles of the tools and applications you build will be better off.

Unfortunately this isn't an option as it is an third party application database. Any tampering would breach our support agreement 😞

[JamesCrandall]

Unfortunately this isn't an option as it is an third party application database. Any tampering would breach our support agreement 😞

How exactly are you "sucking in" the Oracle data as you mentioned in your OP?

[ChrisMatthews]

How exactly are you "sucking in" the Oracle data as you mentioned in your OP?

Apologies, I should have included that info. as it is important; I'm using the Make Query Table tool.

[JamesCrandall]

Apologies, I should have included that info. as it is important; I'm using the Make Query Table tool.

That's my bad.  Missed it.

Well... I wonder if you can perform the conversion directly in the SQL as part of the MakeQueryLayer_management implementation.  I will hunt around to see if there are examples.

My thought is, can you issue a "CAST" or "TO_NUMBER" statement in the sql somewhere?

Edit: I noticed that you are attempting to implement MakeQueryTable!  Man, that stuff just looks messy.  If it were me, I'd go towards python script and implement the cx_Oracle library --- you have so much more control.  I know that is outside of the bounds for your OP and sorry I don't have a good solution other than this.  BUT, the cx_Oracle lib is very awesome 🙂

[ChristopherBlinn1]

Interested also how you're "sucking in" your table.  Plus this sounds like a major flaw on the DB design to store coordinates as strings.

If you need the points, and are willing to do a couple of extra steps then after you "suck in" the data, create a copy of the table (perhaps storing the copy in a FGDB).  Then add your x/y fields (type double), calculate the fields using the string type fields, then make your XY event layer.

If this is a model, and you're not allowing overwrite of geoprocessing events, simply add a step to delete the copied table before creating a new one.

I hate submitting this as a solution, so let's call it a workaround.

[ChrisMatthews]

That's my bad.  Missed it.

Well... I wonder if you can perform the conversion directly in the SQL as part of the MakeQueryLayer_management implementation.  I will hunt around to see if there are examples.

My thought is, can you issue a "CAST" or "TO_NUMBER" statement in the sql somewhere?

Edit: I noticed that you are attempting to implement MakeQueryTable!  Man, that stuff just looks messy.  If it were me, I'd go towards python script and implement the cx_Oracle library --- you have so much more control.  I know that is outside of the bounds for your OP and sorry I don't have a good solution other than this.  BUT, the cx_Oracle lib is very awesome 🙂

Conversion directly in the SQL would be the ideal solution, the model would be alot similiar and the load would be placed on the dB server. I will be exporting the script to Python at some point as it will be run on schedule each night, so the cx_Oracle lib is an option...