[GIS] ExecutING SQL script in ArcGIS field calculator

arcgis-10.0arcgis-desktoperror-999999field-calculatorsql

I am trying to transfer over row data from a table to columns in a shapefile table. There is a common "SA" variable between the two (i.e. the study area number) but there is more than one land use type, hence why I must create two new columns for each landuse, in order to capture the area and percentage value for each type. A join won't work because it only captures the values for one land use as opposed to all of them, as the first table doesn't have as many entries as the second.

The reason I need to migrate the data is because I'm trying to use this in dynamic text on data driven pages.

I wrote a SQL script with the help of my friend but it won't execute through field calculator, giving the generic 99999 error:

UPDATE SApeelfinal
SET LUp1 = (SELECT perc FROM stat_fin WHERE stat_fin.SA = SApeelfinal.FID_text and LANDUSE = 1)
WHERE EXISTS (SELECT 1 FROM stat_fin WHERE stat_fin.SA = SApeelfinal.FID_text and LANDUSE = 1)

Though he said that I have to change the LANDUSE value for each time I run the script and as well change SUM_AREA and perc. I tried putting it in a toolbox but it wouldn't run that way either. Can someone enlighten me as to a better way to do this?

Also I was wondering if I had to manually create the new fields for this to work.

Here is a screenshot of the two tables:
enter image description here

You can also find a bigger version here: http://i.imgur.com/sx08u.jpg

The red is the common variable between the two tables, the blue is the field I want the percentage to go to (and there would obviously be a new one for area), and the green are the two fields I want to migrate over based on the SATXT and FID_text common fields.

I've tried using the following python script in the codeblock:

    def getNewVal(newVal,landuse):
  if landuse == 1:
   return newVal
  else:
   return

With this in the calculate field:

getNewVal(!stat_fin.perc!,!stat_fin.LANDUSE!)

But it returns the same error. Is there something I'm doing wrong?

To clarify, there are no joins because joins make dynamic text not work.

Best Answer

Your need to convert your SQL into VBA or Python for the Field Calculator in ArcMap

http://webhelp.esri.com/arcgiSDEsktop/9.3/index.cfm?TopicName=Making_field_calculations

ArcGIS 10 Users can also use Python scripts in ArcGIS Field Calculator

enter image description here

This is a cheaper option than buying ArcSDE and a database that supports SQL.

Related Question