Make Query Table - help please with SQL!

RobertKnight

Hi

I'm having a bit of a play with the new temporal data support, and am trying to do a one-to-many join using Make Query Table so that I can visualise rainfall readings against weather station locations.

I have a File Geodatabase with a feature class "Rain_Gauges" and a table "Rainfall".

"Rain_Gauges" is a point feature class with fields "Object_ID", "Shape" and "Station_ID", contains 30 points numbered 1-30.

"Rainfall" is a table in the GDB with fields "Object_ID", "Station_ID", "Date_" and "Rainfall", contains numerous rainfall readings from each station over the period of 12 months.

"Station_ID" in both tables provides the common value to make the join.

Using Make Query Table from ArcToolbox, it all looks fairly straightforward and have been following the help instructions. The problem seems to be with the SQL expression to define the join.

Clicking the SQL button to get to Query Builder, am selecting the two fields to create the expression:

Rain_Gauges.Station_ID = Rainfall.Station_ID

Then defining Key_field as Rainfall.ObjectID and running the tool

Getting the error message:-

Error 999999: Error Executing Function
An invalid SQL statement was used [QueryTable]

Could someone please offer some guidance as to the correct SQL syntax to make this work?

Sorry if this seems a simple question, have not used Make Query Table before.

Rob

RobertKnight

Hi

I'm having a bit of a play with the new temporal data support, and am trying to do a one-to-many join using Make Query Table so that I can visualise rainfall readings against weather station locations.

I have a File Geodatabase with a feature class "Rain_Gauges" and a table "Rainfall".

"Rain_Gauges" is a point feature class with fields "Object_ID", "Shape" and "Station_ID", contains 30 points numbered 1-30.

"Rainfall" is a table in the GDB with fields "Object_ID", "Station_ID", "Date_" and "Rainfall", contains numerous rainfall readings from each station over the period of 12 months.

"Station_ID" in both tables provides the common value to make the join.

Using Make Query Table from ArcToolbox, it all looks fairly straightforward and have been following the help instructions. The problem seems to be with the SQL expression to define the join.

Clicking the SQL button to get to Query Builder, am selecting the two fields to create the expression:

Rain_Gauges.Station_ID = Rainfall.Station_ID

Then defining Key_field as Rainfall.ObjectID and running the tool

Getting the error message:-

Error 999999: Error Executing Function
An invalid SQL statement was used [QueryTable]

Could someone please offer some guidance as to the correct SQL syntax to make this work?

Sorry if this seems a simple question, have not used Make Query Table before.

Rob

If it's any help to anyone else, I noticed that the Station_ID fields were of a different type in the feature class compared with the table. Making them both Integer field did the trick.