#1 2017-07-19 13:46:16

Achilie
Member
From: Hamburg, Deutschland
Registered: 2017-01-24
Website

Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi,

i found this error (ORA-00932: Inkonsistente Datentypen: DATE erwartet, NUMBER erhalten) in crystal reports using some date arithmethics.
Here is a simple example:

create a crystal report with this sql-command (i am using oracle):
select *
from user_objects
where created >  {?PM-Datum} - 1.

where PM-Datum is of type date.
This caused the above error.

Best Regards
Joachim

Offline

#2 2017-08-01 11:55:02

jalbrecht
Administrator
Registered: 2016-10-21

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Joachim,

the problem here is an unknown format mask and an implicit conversion that fails. Consider your query:

select *
from user_objects
where created >  {?PM-Datum} - 1.

Here oracle tries to implicitely convert {?PM-Datum}-1 into a date without exactly knowing what format mask ?PM-date might have. In our case try to find out ?PM-Datums format-mask by checking the log wth:

SELECT E.*, P.KEY_FIELD, P.VALUE
FROM RS_AUDIT_LOG_ENTRY E,
RS_AUDIT_LOG_PROPERTY P
WHERE
E.ENTITY_ID = P.LOG_ENTRY_ID

Filter column key_field for value "statement" and column value for "*PM-Datum*". It helps to restrict the datefield! Check the action statement_prepared and open_table_db_data_source. You find the parameter value at the very end of the string. Derive PM-Datum-format-mask and change your query:

select *
from user_objects
where created >  to_date({?PM-Datum}, '?PM-Datums-format-mask') - 1.

Looking forward to hearing from you ...

Jan

Offline

#3 2017-08-01 13:22:59

Achilie
Member
From: Hamburg, Deutschland
Registered: 2017-01-24
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Jan,

thanks for the answer.
I think there is no need to convert the parameter because it is of type date.
The report runs without modification in the designer and i think it will run in InfoView (CrystalReports-Server).
The construct in many of our reports with a date range is:

and start_dtm > {?pm_Start}
and start_dtm < trunc( {?pm_End}) +1 -- we want 23:59:59

I will look in the mentioned tables.

Best regards
Joachim

Offline

#4 2017-08-01 13:28:08

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Joachim,

correct, the parameter already has type=date, BUT you don't control the format you get. In some cases you may get

select *
from user_objects
where created >  '20170130" - 1

in other cases:

select *
from user_objects
where created >  '20173001" - 1

in other

select *
from user_objects
where created >  '2017-30-01" - 1

so in order to control what you give to oracle you can convert it to a date *with the given pattern*:

select *
from user_objects
where created >  to_date({?PM-Datum}, '?PM-Datums-format-mask ( PATTERN )') - 1.

Regards,
Eduardo

Offline

#5 2017-08-01 14:17:13

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

More exactly:

{?PM-Datum} becomes a string when it is passed to the query before handing the query to the JDBC driver for execution.

So with:
to_date({?PM-Datum}, '?PM-Datums-format-mask ( PATTERN )') - 1

we create a date (in the database) and then we can handle date operations (in the database).

Regards,
Eduardo

Offline

#6 2017-08-02 12:24:45

Achilie
Member
From: Hamburg, Deutschland
Registered: 2017-01-24
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Eduardo,

you are right. I monitored the sessions.
The query in crystal was: select * from user_objects where created >  {?PM-Datum}

CrystalReports executes: SELECT * FROM user_objects WHERE created > TO_DATE ('01-08-2017', 'DD-MM-YYYY')
RS executes: SELECT * FROM user_objects WHERE created > TO_DATE ('2017-02-01', 'YYYY-MM-DD')

The formatstring in the to_date function matches the format of the date.
The formatstring comes from crystal not from me.

The original (which gives the error)
CR executes: SELECT *  FROM user_objects  WHERE created > TO_DATE ('01-01-2017', 'DD-MM-YYYY') - 1
RS executes ? It gives me an error.

Regards
Joachim

Offline

#7 2017-08-02 12:47:57

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Joachim,

I don't quite understand: where does the TO_DATE() come from? You say from crystal not from you?
What happens if you change
select * from user_objects where created >  {?PM-Datum}
to
SELECT * FROM user_objects WHERE created > TO_DATE ({?PM-Datum}, 'YYYY-MM-DD')
or similar?

What do you mean by "RS executes ? It gives me an error." ?

Regards,
Eduardo

Offline

#8 2017-08-02 13:16:08

Achilie
Member
From: Hamburg, Deutschland
Registered: 2017-01-24
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Eduardo,

The sql in crystal reports is: select * from user_objects where created >  {?PM-Datum} - 1
without to_date().
When i execute the report in CrystalReportsDesigner the sessionbrowser (for oracle) shows this statement: SELECT *  FROM user_objects  WHERE created > TO_DATE ('01-01-2017', 'DD-MM-YYYY') - 1
When i execute a modified report without "-1" i see that ReportServer executes: SELECT * FROM user_objects WHERE created > TO_DATE ('2017-02-01', 'YYYY-MM-DD')
The dateformat ReportServer uses is YYYY-MM-DD (popup-calendar). There is no problem executing the query.
I cannot see what happens (in the sessionbrowser) with the query if i modify the query with the "-1" because i get an error.

I will not change the query with a to_date(), because the dateformat depends on the GUI and you know the format, see the working query.

Best Regards
Joachim

Offline

#9 2017-08-03 16:50:10

jalbrecht
Administrator
Registered: 2016-10-21

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Joachim,

using dates is always a little tricky because so many participants have a preconceived opinion about the format pattern. Therefore i always control the format pattern myself. In doing so i can avoid implicit conversion incompatibilities based on a RDMS clients or a RDBMS servers or an OS or a VM's Opinion of the format pattern.
Thus said the problem here is to use the date picker that delivers a date and hand out a string to be pasted into the query that can be interpreted in the right way.
This works fine with dynamic lists and Japser Rpeorts and seems to be a little more tricky with chrystal.

In case your report works for you now without the -1 just add it on the other side of the inequation. pls. be aware that the compatibility of all the format masks is just accidential!

wbr jan

Offline

#10 2017-08-04 07:55:47

Achilie
Member
From: Hamburg, Deutschland
Registered: 2017-01-24
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Jan,

but you use the correct pattern when using the query without the "-1" as described below.
The datepicker has the format YYYY-MM-DD and this format is used in the to_date function when executing the report.
The only thing is: what happens with "-1"?
In crystalreports i see to_date(...) - 1 (which is a date) but i cannot see what the sql look like from reportserver.
Once again, the formatmask you apply seems totaly correct.
My guess: instead of TO_DATE ('2017-02-01', 'YYYY-MM-DD') - 1 there is somthing like TO_DATE ('2017-02-01' - 1, 'YYYY-MM-DD') which gives invalid number.

Best regards
Joachim

Offline

#11 2017-08-04 10:16:53

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Joachim,

*we* do not format the date with to_date(), the only thing we do is pass a java.sql.Date to Crystal. Crystal seems to make this to_date() with date parameters and oracle.

I will test tuesday with Oracle, but I tested with MSSQL and it I have this:

My query:
select * from myTable
where f_geb_datum < {?gebDatum}
order by f_geb_datum desc

The query I get when making a Crystal preview:
select * from myTable
where f_geb_datum < {d '2016-08-04'}
order by f_geb_datum desc

The query I get when using reportserver:
select * from myTable
where f_geb_datum < {d '2016-08-04'}
order by f_geb_datum desc

If I use "-", then:

My query:
select * from myTable
where f_geb_datum < {?gebDatum} - 365
order by f_geb_datum desc

In Crystal:
select * from myTable
where f_geb_datum < {d '2016-08-04'} - 365
order by f_geb_datum desc

In reportserver:
select * from myTable
where f_geb_datum < {d '2016-08-04'} - 365
order by f_geb_datum desc

Reportserver only passes a java.sql.Date to CrystalReports. CrystalReports seems to create here a {d '2016-08-04'} for MSSQL. I guess this is the same for Oracle (Crystal would create a to_date()):  I will test this tuesday.

But, whatever the result, the basic idea is that *someone* has to format the expression. In the postings above, we recommended doing this by hand, as we always do in dynamic lists. Here, the situation seems to be a little different, since Crystal does this instead of doing it manually. But the idea is the same.

As you see in my examples, the "- 365" works with MSSQL, I suppose this should work with Oracle too. In your examples without "- 1", the formatting created by Crystal is correct in both cases. But something seems to be happening with the "- 1" expression. Can't you see what exactly arrives at Oracle with select * from user_objects where created >  {?PM-Datum} - 1 ? Since Oracle says: "(ORA-00932: Inkonsistente Datentypen: DATE erwartet, NUMBER erhalten)", something must be arriving. Could you check with some profiler or similar ?

Could you try with the following (or similar) alternatives of "- 1" ?
1. select * from user_objects where created >  {?PM-Datum} - INTERVAL '1' DAY
2.  select * from user_objects where created >  {?PM-Datum} - NUMTODSINTERVAL(1, 'day')
3. maybe there is some problem with the ordering: select * from user_objects where created > ( {?PM-Datum} - 1 )
4. the same for: select * from user_objects where created >  ( {?PM-Datum} - INTERVAL '1' DAY ) and select * from user_objects where created > ( {?PM-Datum} - NUMTODSINTERVAL(1, 'day') )

I haven't tested this, but maybe something is happening with "- 1".

Best regards,
Eduardo

Offline

#12 2017-08-04 12:16:12

Achilie
Member
From: Hamburg, Deutschland
Registered: 2017-01-24
Website

Re: Crystal Reports: ORA-00932: Inkonsistente Datentypen: DATE erwartet

Hi Eduardo,

we just found a simple solution:
select * from user_objects where created > to_date(  {?PM-Datum} ) - 1.
This produces something like this:
SELECT *
  FROM user_objects
WHERE created > TO_DATE (TO_DATE ('2016-04-01', 'YYYY-MM-DD')) - 1.
We suppose this is a bug in the crystal reports java classes.

Your solution - INTERVAL '1' DAY results in:
ORA-30081: Ungültiger Datentyp für Datetime-/Interval-Arithmetik at oracle.jdbc.driver.
I think we can live with the "to_date" workaround.

Thanks and best regards
Joachim

Offline

Board footer

Powered by FluxBB