Query Engine Error

[IanWaterman]

CR10 on Oracle 9i
I have built a report based upon a view.

The view returns data when selected in Toad.

However, when I try to run report I get a failed to return rowset error followed by another error

Query Engine Error : ‘ORA-01861: Literal does not match format string

If I go to the show SQL and then paste that into Toad the query return data without any problem. I do not have any conditions within the select expert. In the data explorer if I check show datatype, all the fields are correctly labeled.

Any suggestions as to why Crystal is refusing to work?

Thank you

Ian

 

[synapsevampire]

Probably because you used an unsupported connectivity type.

Use the Oracle Server (native) as the connection type as opposed to ODBC, or if you insist upon using the less efficient ODBC, make sure that you use the Crystal supplied Oracle ODBC driver, not Oracles.

It could also be something within the query such as an unsupported data type or a reserved word, amongst other things.

-k

 

[IanWaterman]

I am using the Oracle native driver.

I too suspect its something in the query, I do have to test a date which the developer has some how managed to create as a date rather than date time. But then again when I use the actual table in a report its OK.

in the query I have a where clause like this

to_date({DateTimeField}, 'DD/MM/YYYY') >= to_date((DateField}}, 'DD/MM/YYYY')

As I mentioned this is OK in Toad, so not sure why Crystal would object.

Ian

 

[synapsevampire]

Hi Ian, didn't notice it was you.


Try:

to_date({DateTimeField}, 'DD/MM/YYYY') >= (DateField}

It's already a date, you shouldn't be formatting it to a date anyway.

If that fails, try copying the query and pasting it in as a Command object in another report and see what happens.

-k

 

[IanWaterman]

K

That did not work either, so I have just removed that filter from the view and will filter within the report. Not much of an overhead.

Thanks for your help, never thoght that Crystal would object to something within a working view.

Ian

 

[synapsevampire]

That is odd...

One more thing, try changing it to:

to_date({DateTimeField}, 'yyyy/mm/dd') >= (DateField}

And try posting the entire query.

-k

 

[lbass]

Ian,

I think this could be an Oracle issue. Some of my users had a similar problem with the following which worked for me (but not them) in a command:

to_date(to_char({?Fiscal Year}-1)||'-07-01')

I had to change it to:

to_date(to_char({?Fiscal Year}-1)||'-07-01','YYYY-MM-DD')

You might try playing around with the formatting condition.

-LB

 

[IanWaterman]

This is the view

CREATE OR REPLACE VIEW RPT_AC_CONTRACT_CERTAINTY (POLICY_KEY, LOG_CRT_DATE, ATA_POSTING_DATE, ATA_TRANSACTIONTYPE_CDE, LOG_SHORT_DESC,
PLN_NAME, POL_START_DATE, CLI_COMP_NAME, AGT_COMP_NAME, ATA_WCAMIS_REF, CEN_DESC) AS
SELECT POLICY_KEY, LOG_CRT_DATE, ATA_POSTING_DATE,
ATA_TRANSACTIONTYPE_CDE, LOG_SHORT_DESC,
PLN_NAME, POL_START_DATE, NVL(CLI_COMP_NAME, CLI_SURN||', '||CLI_FIRST) CLI_COMP_NAME, NVL(AGT_COMP_NAME, AGT_SURN) AGT_COMP_NAME, ATA_WCAMIS_REF, CEN_DESC
FROM (SELECT LOG_REFERENCE_CDE, LOG_CRT_DATE, LOG_SHORT_DESC FROM EVENTLOG WHERE TRIM(LOG_SHORT_DESC) = 'Email Sent:'),
ACCOUNTTRANSACTIONSET, GENPOLICY, GENPLAN, CLIENT, AGENT, CENTERS
WHERE ATA_REFERENCE_TRANSACTION = LOG_REFERENCE_CDE (+)
AND ATA_REFERENCE_TRANSACTION = POLICY_KEY
AND POL_PLAN_CDE = PLAN_KEY
AND POL_CLIENT_CDE = CLIENT_KEY
AND POL_AGENT_CDE = AGENT_KEY
AND POL_BRANCH_CDE = CEN_PRIMARY_KEY
AND ATA_TRANSACTIONTYPE_CDE IN (20, 21, 23)
AND ATA_REVERSE_INDICATOR = 'N'

When I add the filter

AND TO_DATE(ATA_POSTING_DATE, 'DD/MM/YYYY') >= TO_DATE(LOG_CRT_DATE, 'DD/MM/YYYY')

The view returns data in Toad but Crystal then gives me a query engine error.

If I use

AND TO_DATE(ATA_POSTING_DATE, 'DD/MM/YYYY') >= LOG_CRT_DATE

The view does not return any data. It seems that even though it is showing as a date, Toad seems to interpret as
01/01/2007 00:00:00

As I mentioned I am now doing this filter in Crystal, but would prefer to do on Oracle server if possible.

Thank you

Ian