Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

My original question asked why the where clause below, which includes a sub query, would not work on an SDE database but the same query against a file geodatabase would work. I had thought the problem was in the SQL.

As it turns out, the SQL syntax below is correct. The issue is with the structure of versioned SDE tables. It appears that the sub query select statement will only work on tables with records in the base table. In other words, if the table is versioned without the option to move edits to the base table, the sub query returns no records because the base table is empty. All of the records exist in the version tables. However, if you register the table as versioned WITH the option to move edits to base, the subquery works fine, because values exist in the base table. The sub squery will also work on a non-versioned table.

Not sure I understand why the subquery doesn't see the values in the delta tables. Is there a way to format the SQL so the subquery works on the version table without the base option?

ORIGINAL QUESTION:

The following query works on a table within a File geodatabase:

"GlobalID" IN (SELECT "GlobalID" FROM Inspections WHERE "CompletedDate" > date '2012-01-01 00:00:00')

However, when formating the query for SQL Server and running it against the same table within a SQL Server Express geodatabase, the query returns no records.

GlobalID IN (SELECT GlobalID FROM Storm.DBO.Inspections WHERE CompletedDate > '2012-01-01 00:00:00')

I've tried a variety of different SELECT statements with inner queries against the table in the SQL Server Express geodatabase, and cannot get them to work. What am I missing in the SQL statement?

share|improve this question
    
I think I ran into a issue like this in SQL Server once where there was only one space between the date and time, but SQL Server wanted 2 spaces. Yours looks like one space - try two. –  Chad Cooper Jun 19 '12 at 18:12
1  
I am also running into the same issue with versioned tables. The sub query based on IN statement never returns records from the delta tables. DB: SQL Server 2008 e.g. ID_PIPE_ID IN ( SELECT TAG FROM GISdb.WGIS.cwAbanPipe) When I try to run the same query in say Toad against the delta tables, I can see the expected records. Whata are we supposed to use? –  user8309 Jun 19 '12 at 22:03
    
Fortunately, for my application I am dealing with non-spatial tables and I can register them as versioned with the base option. However, if these were feature classes in a network, this wouldn't be an option. I'd sure like to know if there is a way around this, or wether it is something you just have to deal with. –  Jeff Berry Jun 20 '12 at 20:31
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.