You are not logged in.
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
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
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
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
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
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
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
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
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
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
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
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