DSXchange

I am building a datastage job to populate a SQL Server 2008 target DB and the table has a Geography data type for a geospatial value from the latitude / longitude fields. I'm trying to figure out how to write the upsert statement to calculate and store the value. Below is the SQL statement if I were to run an update on the field to calculate the value after the record is inserted, but hoping there is a way to calculate as I'm updating or inserting the record.

geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

Thanks for any assistance,
JB

Can you not use exactly that expression in your VALUES clause of your INSERT statement or in the SET clause of your UPDATE statement?

I tried that, however the Latitude and Longitude values needed to update this value are on the record being updated/inserted as well. However, I did find a solution to this. Looking at the SQL, it's creating a string so I tested just doing an update/insert to this value using the string in hopes that SQL would transform the value into the "Geography" data type like it does for date fields.

Luckily it does, so I played more with the ODBC to get it to work by passing the string that I derive in a transform stage, no luck. I then used the RDBMS with a SQL Server type and worked successfully!!

Thanks,
JB