ISCHILD function and limitations on the member identifer values (integer)

Ivan Divkovic

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

Gianni Ceresa

@Christian Berg : what a nicely signed integer case

I assume you are using OBIEE as you posted in the OBIEE forum ... What is the type of your employee ID column? Just to make sure it's not this one limiting you, but it can perfectly be an issue with the function where they maybe coded it using signed integers, so ... not many solutions ...

Ivan Divkovic

Yes, I'm using OBIEE, version 11.1.1.7.140715 .

EmployeeID column is defined as DOUBLE in the physical layer and NUMBER in the database.

Ivan