Re: ODBC syntax impact on Oracle 8.1.7

Just used WinSQL to generate the native Oracle SQL and I see that 'ts' is converted to TO_DATE.

Also found that functions only effect index use if applied to columns (though not always a problem) - as I am applying the function to a literal, the index is used.

Should have done a bit more looking before posting!

Oli

mroshaw_at_ukonline.co.uk (Oli Ollerenshaw) wrote in message news:<6342d17e.0405260618.fa06083_at_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 - 16:49:51 CDT