`query` (Apply a query to a table)

The query transaction applies a query to a table.

Description

A query is essentially a transformation on the table that selects a subset of its rows, reorders rows based on one or more sort criteria, summarizes the data, and so on. The result is conceptually another table. Once a query is set using the query transaction, the values in the result table may be retrieved with the getdata transaction.

Query string

The query string in the HTTP header must contain the following parameters:

api

Specify the name of the API transaction.

apiversion

Specify the version of the API that should handle the requested transaction.

For the most up-to-date version, use apiversion=3.

uid

Specify a valid 1010data user name.

pswd

Specify a valid password for the 1010data user name provided to the uid parameter.

Note: The pswd value should be the encrypted password returned from the login transaction.

sid

The session ID for the current API session.

This value is returned by the login transaction and must be provided to every transaction (except login).

kill (optional)

Valid values for kill are as follows:

yes: Terminate the existing session and start a new one.
no: Do not terminate the existing session.
If no session exists, a new one will be created. If a session does exist, an error is returned.

Note: If kill=no and a session is active, the system returns an error.
possess: Log in and possess the existing session.
auth: Authenticate with the system but do not kill, possess, or start any session.

The default is yes.

XML input to server

Input is provided to the API transaction via an XML specification. The outer-most element of the input specification is the <in> element. All input elements and data must be contained by <in>.

<name>: The full name of a table. Table names are specified as a full path to the location of the table, e.g., directory1.directory2.directory[N].table.
<ops>: A list of operations for the system to conduct on a table. This is effectively a 1010data macro.
Note: The <ops> element is required. If you want to return a complete table, you must provide an empty <ops> element that does not contain a 1010data macro.

If <ops> contains one or more <colord> operations, the query transaction takes them into account (i.e., the columns reported in the result will be only those columns selected in the <colord> operations, and they will appear in the order specified in the <colord> operations).

XML response from server

A response from the server contains the following elements:

<rc>: The return code generated by the transaction. For a list of return codes, see Return codes.
<msg>: The message returned by the system. Specific messages correspond to specific return codes.
<nrows>: The number of rows in the table returned.
<table>: A 1010data XML table tree. To see the full XML specification for the table tree, see Table Tree.

The <table> element (see Table Tree) essentially describes the columns of the result. It is equivalent to the result table with zero rows of data (i.e. the <data> element is empty.) The table tree does not contain <title>, <sdesc>, <ldesc>, <link> or <maxdown>. In the event of an error, only the return code and error message are returned.

Query string example

https://www2.1010data.com/gw.k?api=query&apiversion=3&uid=$UID&pswd=$PSWD&sid=$SID

XML input example

<in>
    <name>pub.doc.retail.salesdetail</name>
    <ops>
        <link table2="pub.doc.retail.product" col="sku" col2="sku" suffix="_sm"/>
        <sel value="transid=1400190251"/>
    </ops>
</in>

XML response example

<out>
    <rc>0</rc>
    <msg>query successful</msg>
    <nrows>9</nrows>
    <table>
        <cols>
            <th name="trans_date" type="i" fixed="0">Date</th>
            <th name="store" type="i" fixed="0">Store</th>
            <th name="xsales" type="f" fixed="0">Extended
            Sales</th>
        </cols>
        <data/>
    </table>
</out>

query (Apply a query to a table)