A SQL Custom Query provides the most control over the retrieved documents, by allowing you to specify a database query. This option requires EXTENSIVE knowledge of the system and must be configured correctly.
To configure a SQL query, see Configuring Custom Queries - SQL Query on page 552.
SOURCE: System Admin MRG, page 546 of the 10_0_0 version.
**READ Page 552-553 for NOTES that could be important** System Admin MRG, 10_0_0 version
6. Type the SQL parameters in the clause fields as described in the following table:
**From Clause** Type the database table names to use for the query. Use commas to separate table names. Note:Table aliases are not supported.
**Where Clause** Type the specific criteria for the query. This can include joins to tables given in the From Clause as well as specific column values for the table.
**Order By Clause** Type the criteria that will define the sort order of the results, such as datestored (date stored) or [column name] asc (ascending), or desc (descending), where [column name] is the column used for sorting.
Note: The query engine will always use the fully qualified SQL table reference 'hsi.itemdata' in the SELECT clause for every column that needs to be returned. In addition, a SELECT COUNT will always use Count(hsi.itemdata.itemnum). Because of this, these queries should not be configured with a table alias for hsi.itemdata, as doing so will cause execution problems.
For example, "select count(hsi.itemdata.itemnum) from hsi.itemdata itd where itemnum = X"
will generate an error because of the inclusion of the alias itd.
7. Click Save.
8. Configure the remaining available options as described under Configuring Custom Queries on page 543.
SOURCE: System Admin MRG, page 554 of the 10_0_0 version.
EXAMPLE:
From Clause
hsi.itemdata LEFT JOIN hsi.doctype ON hsi.itemdata.itemtypenum = hsi.doctype.itemtypenum
Where Clause
hsi.doctype.itemtypenum = '107'
Order By Clause
hsi.itemdata.itemnum asc
