SELECT SQL Statement

The supported syntax is:

SELECT <attributes> FROM <table> <with ncsheader> <where>

Each document in an application defines both a table and a view. The table name is the name of the document, and the view name is "Simple View of " followed by the document name. In addition, each application contains a table called MASTER and a corresponding "Simple view of MASTER." A view name can be used anywhere a table name is. Column names are the fields defined for a document plus the field names defined by the NCS Header if the "include NCS Header" property is set or the with (ncsh) hint is used.

NOTE: The NCSHEADER and NCSH table hints are interchangeable.

Only a single table can be specified.

The table name or ordinal can be used in place of the command, and is a synonym for "select * from <table>".

The WHERE clause is always optional, and if not specified all rows are affected. Sub-queries are not supported in the WHERE clause, but arbitrary Boolean expressions can be used to select rows based on the values found in the row, as in "SELECT name, score from form1 where (teacher = ‘williams’ and grade = ‘6’) or grade = ‘5’.

You can use the document number instead of the document name. This is not as much an optimization as a convenience considering that the document titles are typically verbose. Keep these rules in mind:

• Document number must be specified in decimal format like 1, 2, 5 and so on.
• Document number 0 maps to the Master document.
• Do not use leading zeros for the document number.
• Specifying an invalid document number will cause a SQL parse error.
• We discourage the practice of hardwiring document numbers.

The SELECT statement also supports the FOR XML clause.

Element Normal Form

Each row in the result set becomes an element with the generic name “row”. Each column becomes a sub-element named after the column. The resolved data for the column becomes the content of the element. There are as many sub-elements as columns in the result set.

<row>

<Name>QUARTER RIGHT></Name>

<Gender>F</Gender>

<Grade>00</Grade>

<Birthdate>02281978</Birdthdate>

<Clips></Clips>

<ROWID>000232</ROWID>

</row>

Attribute Normal Form

Scanned data appears as attribute content. This is the least verbose form.

<row Name=”QUARTER RIGHT” Gender=”F” Grade=”00” Birthdate=”02281978”

Clips=”” ROWID=”000232” />

Archive Indices

Archive Indices [Archive Indices] is a provider-specific SQL column selector construct that can be used to query the Archive Indices thus:

select [Archive Indices] from [GPAS #221666 Dual Sided]

The above query will create a result set whose columns collection contains all of the fields of the Archive Indices. Each row in the result set contains the index values themselves.

Note the (null) Birthdate and the all-blanks Score column. The Scantron OLE DB Provider interprets all-blanks in a Date field as DB null.

Syntax:

Select [Archive Indices] from <table-name>

Select [Archive Indices] from <simple-view-name>

Select <table-name>.[Archive Indices] from <table-name>

You can combine [Archive Indices] with other column selectors:

Select [Serial Number],[Archive Indices] from <table-name> with (NCSHEADER)

Syntactically correct but probably has limited usefulness:

Select *,[Archive Indices] from <table-name>

NOTE: If the document has no fields marked as archive indices, [Archive Indices] will resolve to an empty list of columns. The query will fail with the error message "No archive indices are defined for the document." (OPL_E_ARCHIVEINDICESEMPTY). As a conservative policy, this happens even if other column selectors are present in the query.

There is no way to specify an alias for the columns in the query (just like select *)

You can also use "Archive Indices". The delimiters are necessary because of the embedded space.

Do not create a field with the name "Archive Indices" in the application - behavior will be unpredictable.

See also

SQL Statements Accepted by the Data Provider

SQL Best Practices

FOR XML Syntax Errors Reference