2 Replies Latest reply on Dec 7, 2016 11:43 AM by Ivan Divkovic

    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