SelectSELECT

SELECT <columns> FROM <table> <with ncsh> <where>

The select statement returns data from a table without changing it.

The <columns> specifier is either a * (for all columns in the table), a column name, or a comma separated list of column-names. Column names can optionally be prefixed with the table name followed by a period (.). Column names containing spaces must be delimited with square brackets ([]) or double quotes ("").

Columns may be aliased by using the form column AS alias.

SELECT [Serial Number] AS Num, [SSN] FROM 2 WITH (NCSH)

A special column selector of [archive indices] is supported which will expand into the set of all columns that are marked as archive index fields in the ScanTools Plus application definition. The [archive indices] column can not be aliased.

SELECT [archive indices] FROM 2

Two special aggregate functions are also defined: RAWTIFF() and RICHTIFF(). These functions return multi-image TIFF files created from the CLIPS or CLIPBYTES columns. Aggregate functions can not be mixed with single columns, so if present these functions must be the only columns specified. See Clips Table Overview for additional information about multi-image TIFF files created from the CLIPS or CLIPBYTES columns.

SELECT RICHTIFF(ClipBytes) AS Tiff FROM clips WHERE [serial number] = 1 AND childid = 0

The <table> specifier is the name or number of an existing table or view. Each document defined in the application definition defines a table and the table name is the document name. Documents can also be referred to by document number, so specifying table 1 will select from the table whose ScanToolsPlus document number is 1.

SELECT * FROM [Image Sample Form #254963]

SELECT * FROM 2

Each document also defines a view, named "Simple view of X", where X is the document name.

SELECT [Birthday], SSN FROM [Simple View of Image Sample Form #254963]

In addition to tables created by ScanToolsPlus documents, the tables master, clips, and [edit failures] will always exist. The table master also has an associated view, "Simple view of master".

SELECT [Serial Number], [Batch Number], Ascii FROM Master

SELECT FullClipID, ClipID, [Serial number] FROM clips

SELECT * from [Edit Failures]

The <with ncsh> specifier is optional. If present, the columns defined in the NCS Header are included in the table definition and can be used in the query.The form of the specifier is "with (ncsh)".If the "Include NCS Header" connection property is set to true, then the NCS Header columns will always be present, even if <with ncsh> is not specified.The master table always includes NCS Header columns as well.

SELECT * FROM 2 WITH (NCSH)

The <where> clause is optional, and is a boolean expression used to select records. The following operators can be used:

• AND, OR, NOT, ()
• +, -, *, /
• LIKE, NOT LIKE
• IS NULL, IS NOT NULL

The LIKE and NOT LIKE operators apply to string columns and use the % sign as a wild card on either the start of a string (name LIKE ‘%bar’), the end of a string (name LIKE ‘foo%’), or both (name LIKE ‘%ooba%’(.

Rows will be returned by the SELECT statement if the WHERE clause evaluates to true. If no WHERE clause is present, all rows will be selected.

SELECT * from [Edit Failures] where [serial number] < 20 and [serial number] > 10

The [CLIPS], [IFD OFFSET], [COUNT OF CLIPS], [OFFSET], and [LENGTH] columns are image clips related. If the application does not use the image header fields, these columns will be NULL.

SELECT Clips from Master where [IFD OFFSET] IS NOT NULL

See Also

SELECT SQL Statement for additional information.