0 Replies Latest reply on Apr 20, 2017 3:18 AM by VincentHu-Quest

    OBIEE XmlViewService.executeSQLQuery do not show decimals on real columns

    VincentHu-Quest

      Hi Guys,

      We have encounter on issue in OBIEE, the interface we used is XmlViewService.executeSQLQuery .

       

      We use this method to retrieve data from Oracle Business Intelligence server into Excel spreadsheets. I use a very simple query and it works properly, with one exception: numeric data is rounded (or maybe truncated) to zero decimal places. When I run an identical query through the Oracle BI web interface, I get the decimal places; so, I know something maybe wrong on this method.

       

      We google this issue, find similar post before: OBIEE WebService XmlViewService.executeSQLQuery result auto trunc double to int   , but the suggestion is to use XmlViewService.executeXMLQuery instead of XmlViewService.executeSQLQuery .

      But we do need to use XmlViewService.executeSQLQuery, for our input is a sql query.

       

      Given that we have confirmed the decimals are not present in the XML data sent by OBIEE, even though the header information sent indicates that the field is of type double (see except below of the rowset returned as captured via Wireshark); that the same query statement executed within the answers interface does include decimals; and that there does not seem to be any relevant parameter in the query request structure that would affect the returned decimals; I find it difficult to think that this is anything but a bug in the executeSQLQuery service method.

       

      <rowset xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" >

        <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:saw-sql="urn:saw-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset">

         <xsd:complexType name="Row">

      <xsd:sequence>

      <xsd:element name="Column0" type="xsd:double" minOccurs="0" maxOccurs="1" saw-sql:type="double" saw-sql:displayFormula="saw_0" saw-sql:tableHeading="DimProduct" saw-sql:columnHeading="UnitPrice" />

      </xsd:sequence>

      </xsd:complexType>

        </xsd:schema>

        <Row>

         <Column0>1</Column0>

        </Row>

        <Row>

         <Column0>2</Column0>

        </Row>

        <!-- ... -->

      </rowset>

       

      Thanks in advance for the reply.