Querying A layer that has it's table joined

LanceCrumbliss · ‎06-30-2010

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

DominiqueBroux · ‎07-01-2010

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?

LanceCrumbliss · ‎07-01-2010

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

DarinaTchountcheva · ‎07-01-2010

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

LanceCrumbliss · ‎07-01-2010

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

DominiqueBroux · ‎07-01-2010

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...

LanceCrumbliss · ‎07-05-2010

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: