laptop and a wrench

Error

Query Builder does not build the proper date query when the layer has a join and all records are kept. Also, manually writing the expected SQL query for dates when joins are present does not work. This issue effects all GDBs (PGDB, SQL Server, Oracle) except FGDB.

Última publicación: August 25, 2014 No Product Found
Número de ID del error NIM004401
EnviadoSeptember 27, 2006
Última modificaciónApril 28, 2025
Relacionado conNo Product Found
Versión encontrada9.0
EstadoWill Not Be Addressed

Información adicional

No Public Explanation

Solución alternativa

Use FGDB - there are no problems with above queries when using FGDB. For other databases, include a field from the other side of the join. For example:If you are using PGDB, modify the query to be the following:left hand side of the join: FC1.date = date '01/12/2001' and Table1.OBJECTID > 0right hand side of the join: Table1.date = date '01/12/2001' and FC1.OBJECTID > 0For SQL Server:vtest.MAP.FC1.MYDATE = date '2008-10-01 12:35:41' AND vtest.MAP.Table1.OBJECTID IS not NULLFor Oracle:left side: MAP.FC1.MYDATE = date '2008-10-01 12:35:41' and MAP.table1.OBJECTID is not nullright side: MAP.TABLE1.MYDATE = date '2008-10-01 12:35:41' and MAP.FC1.OBJECTID is not nullThis is documented in the help: <a href="http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?id=257&pid=247&topicname=SQL_reference" target="_blank">http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?id=257&pid=247&topicname=SQL_reference</a>It is also documented in KB22132:<a href="http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=22132" target="_blank">http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=22132</a>Also, if the joined tabled has nulls (i.e. there were some records that didnt match), and you want to query dates on the left hand side of the join including records that dont have a match on the right hand side, then you can run this query (for PGDb):FC1.MYDATE = date '02/10/2008 8:51:59' AND (Table1.OBJECTID IS NOT NULL OR Table1.OBJECTID IS NULL)The OR statement selects all records on the right hand side of the join.This is what it would look like for SQl Server:vtest.MAP.FC1.MYDATE = date '2008-10-01 12:35:41' AND (vtest.MAP.table1.OBJECTID IS NULL or vtest.MAP.Table1.OBJECTID IS NOT NULL)This is what it would look like for Oracle:MAP.FC1.MYDATE = date '2008-10-01 12:35:41' and (MAP.Table1.OBJECTID is null or MAP.Table1.OBJECTID is not null)

Pasos para reproducir

ID del error: NIM004401

Software:

  • No Product Found

Recibir notificaciones cuando cambie el estado de un error

Descargar la aplicación de soporte de Esri

Descubrir más sobre este tema

Obtener ayuda de expertos en ArcGIS

Contactar con el soporte técnico

Descargar la aplicación de soporte de Esri

Ir a opciones de descarga