Oracle SQL error through ODBC connection

[BarryCrosby]

I'm getting an Oracle error message when I try to amend the SQL generated from Crystal.

I'm using Oracle 8.0.4, Crystal 8.5 connecting via the microsoft ODBC connection.

The original sql is:

SELECT
REPORTING_DATES."REPORTING_DATE",
INCIDENT."INCIDENT_REF"
FROM
"SA"."REPORTING_DATES" REPORTING_DATES,
"SA"."INCIDENT" INCIDENT
WHERE
REPORTING_DATES."REPORTING_DATE" = INCIDENT."DATE_LOGGED" (+)

The problem is the reporting_dates field is only populated with the date, no times. I am trying to link by using the TRUNC function in the SQL:

SELECT
REPORTING_DATES."REPORTING_DATE",
INCIDENT."INCIDENT_REF"
FROM
"SA"."REPORTING_DATES" REPORTING_DATES,
"SA"."INCIDENT" INCIDENT
WHERE
trunc(REPORTING_DATES."REPORTING_DATE") = trunc(INCIDENT."DATE_LOGGED") (+)

Unfortunately I keep getting the error:

ODBC Error: (Microsoft)(ODBC Driver for Oracle)(Oracle)ORA-00933: SQL command not properly ended.

Any ideas how I can resolve this?

Thanks for any help in advance,
Barry

 

[vidru]

If you can, create views for the two tables. Within the view definition(s), format the dates using the TRUNC function so you'll be able to link them correctly.

-dave