topic Re: Querying A layer that has it's table joined in ArcGIS API for Silverlight Questions

Querying A layer that has it's table joined

LanceCrumbliss — Wed, 30 Jun 2010 18:08:15 GMT

hi all,

i'm trying to perform a query task on a layer that has it's table joined to a standalone table in a geodatabase. the table is included in the published msd file, and the web user has the correct permissions (both the layer and the table are within the same geodatbase, actually) but a query of "1=1" returns an invalid sql error. is querying a joined table not supported? for the record, performing an ID task and find task on the same layer will work fine, showing the attributes from the joined table.

further, on queries that do work on other layers in the map, sometimes geometry is returned, other times it is not. return geometry is set to true. again, ID tasks and find tasks return the geometry correctly.

using the RC API.

lance

Re: Querying A layer that has it's table joined

DominiqueBroux — Thu, 01 Jul 2010 07:58:48 GMT

is querying a joined table not supported

It is supported and it's generally working. I can't reproduce your issue.
Is your query working if you are just using the REST API?

sometimes geometry is returned, other times it is not

This can happen if the SHAPE field is not visible. I guess it's not your case if find task is working but worth the verification. Else same question, is it working with the REST API only?

Re: Querying A layer that has it's table joined

LanceCrumbliss — Thu, 01 Jul 2010 10:31:11 GMT

It is supported and it's generally working. I can't reproduce your issue.
Is your query working if you are just using the REST API?

i've attached two screenshots of attempting the REST API method. the first is showing the layer info, the second showing the result of the query. i get the same error.

This can happen if the SHAPE field is not visible. I guess it's not your case if find task is working but worth the verification. Else same question, is it working with the REST API only?

you are correct, the SHAPE field was hidden. unhiding it seems to resolve that particular issue. odd that ID and Find tasks returned geometry, isn't it?

lance

Re: Querying A layer that has it's table joined

DarinaTchountcheva — Thu, 01 Jul 2010 12:30:27 GMT

Lance,

How many records do you expect to be returned by the query when the where clause is 1=1?
Is it it the 500 records that a service defaults to, or you have changed the maximum number to a bigger than 2000 records?

I have not tried your scenario, but I have seen this error in the situation explained in this thread:

http://forums.esri.com/Thread.asp?c=158&f=1696&t=297004,

And since you have a business table joined, it might be related.

And here is another thread that is kind of related:

http://forums.esri.com/Thread.asp?c=158&f=2421&t=303159&mc=2#msgid948261

To test this, try running a query that returns only a small number of records. If it works, try changing the max number of records for the service to a number smaller than 2000, and run again the 1=1 query. See if you will get results back.

Good Luck!

Darina

Re: Querying A layer that has it's table joined

LanceCrumbliss — Thu, 01 Jul 2010 12:48:35 GMT

Lance,

How many records do you expect to be returned by the query when the where clause is 1=1?
Is it it the 500 records that a service defaults to, or you have changed the maximum number to a bigger than 2000 records?

I have not tried your scenario, but I have seen this error in the situation explained in this thread:

http://forums.esri.com/Thread.asp?c=158&f=1696&t=297004,

And since you have a business table joined, it might be related.

And here is another thread that is kind of related:

http://forums.esri.com/Thread.asp?c=158&f=2421&t=303159&mc=2#msgid948261

To test this, try running a query that returns only a small number of records. If it works, try changing the max number of records for the service to a number smaller than 2000, and run again the 1=1 query. See if you will get results back.

Good Luck!

Darina

hi darina,

the max records is set to the default. there are only 30 records in the layer's table anyway. for grins, i tried "PIPELINE_CENTERLINE.OBJECTID = 1" using the REST API but get the same error.

lance

Re: Querying A layer that has it's table joined

DominiqueBroux — Thu, 01 Jul 2010 15:26:13 GMT

the first is showing the layer info, the second showing the result of the query. i get the same error.

So it's not a Silverlight API issue :cool: .... but anyway an issue:confused:
The only strange thing I saw in your screenshots is that the Display Field is 'ProjectName', I thought it should be 'GIS_Info.ProjectName'.
Likely this doesn't explain your issue but could worth the try to reinitialize the display field in ArcMap before publishing the service.
Other question : Is 'Open Attribute Table' working well on this layer in ArcMap ?

you are correct, the SHAPE field was hidden. unhiding it seems to resolve that particular issue. odd that ID and Find tasks returned geometry, isn't it?

If though as well that ID and Find tasks would not return geometry if SHAPE field was hidden, but...

Re: Querying A layer that has it's table joined

LanceCrumbliss — Mon, 05 Jul 2010 15:58:10 GMT

So it's not a Silverlight API issue :cool: .... but anyway an issue:confused:
The only strange thing I saw in your screenshots is that the Display Field is 'ProjectName', I thought it should be 'GIS_Info.ProjectName'.
Likely this doesn't explain your issue but could worth the try to reinitialize the display field in ArcMap before publishing the service.
Other question : Is 'Open Attribute Table' working well on this layer in ArcMap ?

grrr, nope. 😞 it still doesn't work in the app or the REST web interface after reinitializing the display field and publishing. but yes, the table works fine within ArcMap itself. :confused: