ORA-00932 Inconsistent datatype: Expected CHAR got DATE

[Kebabmeister]

I have the following fragment of SQL as part of a textual query in the Actuate reports application

(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, in Actuate it throws up this error. Does anyone know what it is?

 

[SantaMufasa]

Kebab,

Can you please post the results of running this SQL*Plus command:

describe wostatus

Thanks

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Kebabmeister]

Hi Dave

That command in Toad just brings up a pop-up window which I can't copy and paste. I assume you are wanting to know the data type of changedate, which is "Date".

 

[SantaMufasa]

Kebab,

My approach (if I were in your position) would be to execute pieces of the code, using your data values from the failing execution, until I isolated the problem.

I'm sorry that I cannot be more specific, but without your actual table values and bind-variable values, I cannot give you a definitive reason for your problems.

Please update us on your finding as you do piece-meal executions of your code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

perform the describe on the table in sqlPlus, not toad.

Bill
Oracle DBA/Developer
New York State, USA

 

[BJCooperIT]

Is it possible that :upper & :lower are dates in Toad but strings in Actuate?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Oooooo, good thought, Barb. Let's hope Kebab reads this soon and let's us know if that's the problem, and you get the troubleshooting prize.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Kebabmeister]

Using Dave's suggestion of piece-meal executions, I tracked the problem down to the parameters 'lower' and 'upper'. If they are removed and substituted with 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 actuate Oracle library is AcOrcl81.dll whereas we are running maximo on oracle 9. I think my version of TOAD has the oracle 9 drivers, whilst my colleagues, with older TOAD installations, have oracle 8 drivers.

I achieved this programmatically in the end so it's less important to solve the problem (for now). Trouble is, I just know I'm going to run into this again. Does what I suggest above sound feasible?

 

[stefanhei]

Hi,

I think BJCooperIT gave you the correct hint.
Try to convert those variables into dates with to_date as you did with the literal values.
The reason why the statement works when you use TOAD but not when your colleages try, may be due to differnt datespecific settings on your workstations, which affect how dates are displayed and processed. The dateformat your client uses can be used for automatic conversion, the one of your colleages cant.

Stefan