Getting the Query Plan

Returns the query plan of the given data provider.

The query plan represents the SQL statements that compose the query.

Request

GET <webiURL>/documents/{documentId}/dataproviders/{dataProviderId}/queryplan

Where:

  • {documentId} is the document identifier retrieved from the list of documents
  • {dataProviderId} is the identifier of the data provider available for a document
Response

Response type: application/xml or application/json

Response body: the details of the query plan, which is made of a series of SQL statements combined using joins and combination operators such as UNION, INTERSECT, and MINUS.

Example

GET <webiURL>/documents/9106/dataproviders/DP2/queryplan

<queryplan custom="false" editable="true">
    <union>
        <fullOuterJoin>
            <statement index="1">SELECT 'FY' || to_char(SALES.invoice_date,'yy'),
                count(distinct SALES.inv_id) FROM SALES GROUP BY 'FY' ||
                to_char(SALES.invoice_date,'yy')</statement>
            <statement index="2">SELECT 'FY' || to_char(SALES.invoice_date,'yy'),
                sum(INVOICE_LINE.nb_guests) FROM SALES, INVOICE_LINE, SERVICE_LINE,
                SERVICE WHERE (SALES.INV_ID=INVOICE_LINE.INV_ID) AND 
                (INVOICE_LINE.SERVICE_ID=SERVICE.SERVICE_ID) AND 
                (SERVICE.SL_ID=SERVICE_LINE.SL_ID) AND
                (SERVICE_LINE.service_line ='Accommodation') 
                GROUP BY 'FY' || to_char(SALES.invoice_date,'yy')</statement>
        </fullOuterJoin>
        <fullOuterJoin>
            <statement index="3">...</statement>
            <statement index="4">...</statement>
        </fullOuterJoin>
        <intersect>
            <fullOuterJoin>
                <statement index="5">...</statement>
                <statement index="6">...</statement>
            </fullOuterJoin>
            <fullOuterJoin>
                <statement index="7">...</statement>
                <statement index="8">...</statement>
            </fullOuterJoin>
            <minus>
                <fullOuterJoin>
                    <statement index="9">...</statement>
                    <statement index="10">...</statement>
                </fullOuterJoin>
                <fullOuterJoin>
                    <statement index="11">...</statement>
                    <statement index="12">...</statement>
                </fullOuterJoin>
            </minus>
        </intersect>
    </union>
</queryplan>

Example

If the data provider has contexts, they must be resolved first.

GET <webiURL>/documents/8722/dataproviders/DP5/queryplan

Response

<error> 
    <error_code>WSR 00103</error_code>  
    <message>Missing contexts for the data provider "DP5".</message> 
</error>

GET <webiURL>/documents/8722/dataproviders/DP5/parameters?refresh=false

The request body contains an answer to the context:

<parameters>
    <parameter>
        <id>0</id>
        <answer>
            <values>
                <value id='_VlxrQPzQEeG4Q-3y4CsnNg'>INVOICE_LINE</value>
            </values>
        </answer>
    </parameter>
</parameters>

The response shows a parameter of type prompt.

This parameter does not need to be answered at this level:

<parameters> 
    <parameter optional="false" type="prompt" dpId="DP5"> 
        <id>1</id>  
        <technicalName>pmEnter value(s) for Service</technicalName>  
        <name>Enter value(s) for Service</name>  
        <answer constrained="false" type="Text"> 
            <info cardinality="Multiple"> 
                <lov hierarchical="false" partial="false" refreshable="true" searchable="true"> 
                    <id>UNIVERSELOV_DS2.DO27</id>  
                    <updated>2014-08-05T12:41:14.000+02:00</updated>  
                    <values> 
                        <value>Activities</value>  
                        <value>Bungalow</value>  
                        <value>Car Rent</value>  
                        <value>Excursion</value>  
                        <value>Fast Food</value>  
                        <value>Hotel Room</value>  
                        <value>Hotel Suite</value>  
                        <value>Poolside Bar</value>  
                        <value>Restaurant</value>  
                        <value>Sports</value>  
                        <value>Travel Reservation</value> 
                    </values>  
                    <columns mappingId="0"> 
                        <column id="0" type="String">Service</column> 
                    </columns> 
                </lov> 
            </info> 
        </answer> 
    </parameter> 
</parameters>

GET <webiURL>/documents/8722/dataproviders/DP5/queryplan

Response:

<queryplan custom="false" editable="true"> 
    <statement index="1">
        SELECT "CITY"."CITY", "SERVICE"."PRICE" FROM "CITY", 
        "SERVICE", "INVOICE_LINE", "SALES", "CUSTOMER" WHERE ( "INVOICE_LINE"."SERVICE_ID"
        ="SERVICE"."SERVICE_ID" ) AND ( "SALES"."INV_ID"="INVOICE_LINE"."INV_ID" ) AND
        ( "CUSTOMER"."CUST_ID"="SALES"."CUST_ID" ) AND ( "CITY"."CITY_ID"="CUSTOMER"."CITY_ID" )
        AND "SERVICE"."SERVICE" IN @Prompt('Enter value(s) for Service','A','Service\Service',
        Multi,Free,Not_Persistent,,User:0)
    </statement> 
</queryplan>