[gdal-dev] SQL fails with date query to SDE table

[Clay, Bruce]

I am working on a tool to merge attributes from 2 tables into a 3rd
table.  Everything works fine if both tables are SDO_GEOMETRY enabled
and I use the OCI driver.  If the tables are in Oracle but not
SDO_GEOMETRY enabled they don't show up using the OCI driver.  I can use
can use a query like:

 

SELECT ACTIVE_CONFIDENCE K_ACTIVE_CONFIDENCE, ALT_SD K_ALT_SD, ALTITUDE
K_ALTITUDE,

   "TO_CHAR(UPDATE_DATETIME , 'MM-DD-YYYY HH24:MI:SS'),"

   WKOKMO_5 K_WKOKMO_5, SHAPE

   FROM

      DATA_PER_ICD_W_SSS

   WHERE

      UPDATE_DATETIME BETWEEN TO_DATE('10/19/2007 14:37:57' ,
'MM-DD-YYYY HH24:MI:SS') AND

      TO_DATE('10/22/2007 14:37:57' , 'MM-DD-YYYY HH24:MI:SS') 

 

 

If I try the same thing with an ArcCatalog created table using the SDE
driver the program fails in the swq.c file called from the ogrDataSource
ExecuteSql statement.

 

I modified the query to the following

 

"SELECT ACTIVE_CONFIDENCE, ALT_SD, ALTITUDE,

 UPDATE_DATETIME,

 WKOKMO_5 

 FROM GEODB. DATA_PER_ICD_W_SSS WHERE 

 UPDATE_DATETIME >= '10/19/2007 14:37:57' AND 

 UPDATE_DATETIME <= '10/22/2007 14:37:57';

 

And I no longer get any errors but I don't get any data either.  The
original statement used TO_CHAR and TO_DATE which are normal for Oracle
SQL statements.  TO_DATE works fine in the SDE GetFeatureCount but fails
getting data.

 

The BETWEEN of the orignal statement is not supported by the methods in
swq.c.  I added it to the logic in swq_identify_op and got past that
error but then received extra token error in swq_expr_compile2.  Then I
changed to the above statemnet and got past all of the error messages
but still no data.

 

Does anyone see anything wrong in the SQL or had similar problems and
know how to get around them?

 

Does OGR support date comparisons outside of Oracle?  If so what is the
correct format?

 

 

Thanks for your thoughts and suggestions.

 

Bruce

 

 




This message and any enclosures are intended only for the addressee.  Please  
notify the sender by email if you are not the intended recipient.  If you are  
not the intended recipient, you may not use, copy, disclose, or distribute this  
message or its contents or enclosures to any other person and any such actions  
may be unlawful.  Ball reserves the right to monitor and review all messages  
and enclosures sent to or from this email address.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20080325/632b1152/attachment-0001.html