ORA-00932 Inconsistent datatype: Expected CHAR got DATE

[Kebabmeister]

I have the following fragment of SQL as part of a textual query

(Select provider, Count(*) As CountOfPrev From wostatus, workorder where workorder.wonum = wostatus.wonum and
wostatus.wonum in (Select wonum from workorder where siteid = 'N304' and location = :site) and wostatus.status = 'LOGNOTE2' and
((Trunc(wostatus.changedate) - (To_Date('01/01/1990','dd/mm/yyyy'))) >= (lower - (To_Date('01/01/1990','dd/mm/yyyy'))) -
upper - :lower))) and
((Trunc(wostatus.changedate) - (To_Date('01/01/1990','dd/mm/yyyy'))) <= (Upper - (To_Date('01/01/1990','dd/mm/yyyy'))) -
upper - :lower))) Group by workorder.provider) SubQuery2

Although this works in Toad, when I hit the "Describe Query" button in Actuate it throws up this error. Does anyone know what it is?

 

[ASG0856]

Wouldn't swear to this however, It looks like you are trying to count days. However, I do know that Actuate Sql treats '-' as 'between' in although it may not be in this case, and I don't think it accepts To_Date. However, Im probabley completely off track

 

[gdwood]

Are you certain this Trunc(wostatus.changedate) gives you a date and not a char? And the previous post shoudl have you in the right direction, sql doesn't like - between dates it will probably not give you a date as a result

 

[Kebabmeister]

Thanks for your responses. In PLSQL "Trunc" applied to a date removes the time component to leave you with a date (See below taken off a PLSQL tech site)

In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure.

The syntax for the trunc function is:

trunc ( date, [ format ] )

date is the date to truncate.

format is the unit of measure to apply for truncating. If the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

I tracked the problem down to the parameters 'lower' and 'upper'. If they are removed and substituted for a db datetime field then the SQL is fine in actuate. Furthermore, colleagues trying to run the SQL in TOAD found the same failure where I had no problem in TOAD. This suggests a driver issue to me. Further investigation shows that our Oracle library is AcOrcl81.dll whereas we are running maximo on oracle 9. I think my version of TOAD has the oracle 9 driver, whilst my colleagues have oracle 8 driver.

In the end I have decided to achieve this programmatically instead of through the SQL. Many thanks for your efforts: there are few enough Actuate Developers on here that we should nurture these collaborations!!