ODBC syntax impact on Oracle 8.1.7

From: Oli Ollerenshaw <mroshaw_at_ukonline.co.uk>
Date: 26 May 2004 07:18:44 -0700
Message-ID: <6342d17e.0405260618.fa06083@posting.google.com>

Hi all,

I am running Crystal Reports 8 against an Oracle 8.1.7 database, using the supplied Crystal Oracle ODBC driver.

One of the queries, generated by the report, references a date/time field: 'CREATED'.

When viewing the source through Crystal, the SQL looks something like the following:

SELECT
    S_ASSET_XM."CREATED", S_ASSET_XM."ATTRIB_17", S_ASSET_XM."ATTRIB_36",
FROM
    "SIEBEL"."S_ASSET_XM" S_ASSET_XM
WHERE     S_ASSET_XM."CREATED" >= {ts '2004-05-26 00:00:00.00'} AND     S_ASSET_XM."CREATED" < {ts '2004-05-26 23:59:59.00'}

I have created an index on the 'CREATED' column.

My Oracle DBA person says that this index will not be used, as we are applying a 'function' to the CREATED column value. She is referring to the 'ts' identifier in the WHERE clause.

My assumption was that this is an ODBC identifier that is not passed down to Oracle - is this correct?

Will the query above use my index or not?

Any thoughts are very much appreciated!

Regards,

Oli Received on Wed May 26 2004 - 09:18:44 CDT