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?
