Hi there,
I'm using the 11.1.1.7.140715 version of OBIEE on Linux 6 and I've encountered some problems with the IsChild function on a hierarchy column when the member identifier (second parameter of the IsChild function) is larger then 2,147,483,647
For example if I use a simple logical SQL like this one:
select employees.FirstName, employees.employeeID
from ValueHierarchy
where IsChild(employees.employees, 2147483647);
I will NOT get an error but if I set a value that is incremented by 1 then I get a syntax error:
select employees.FirstName, employees.employeeID
from ValueHierarchy
where IsChild(employees.employees, 2147483648);
This is the error:
Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P
Location: saw.views.evc.activate, saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool.socketrpcserver, saw.threads
Odbc driver returned an error (SQLExecDirectW).State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <2147483648>: Syntax error [nQSError: 26012] . (HY000)
I've found a document on support.oracle.com (2028701.1) that says that you cannot use negative number in then member name of IsChild and IsDescendant functions (if you are using 11.1.1.7.140715 version of OBIEE because the problem is solved in 11.1.1.9.x)
But what about positive numbers? It seems that the member identifier parameter of the IsChild function accepts only positive integers since the greatest number you can use as a parameter value is 2^31 (2 power 31)?!
Did anyone had such problems and is it possible to solve it in this version because the upgrade on 11.1.1.9.x is not an option for now?!
thank you in advance for any help..
best regards,
Ivan
