|
The base query is the base SQL query that the system will use to populate the fields available to use for the search.
NOTE: In order to setup a base query you must have knowledge of the Seradex database structure related to tables and fields. Having the skill set to develop database queries and views is also recommended.
This guide will not be covering the concepts of SQL, and Seradex database structure related to tables and fields. Please consult your Project Manager if assistance is required in these areas.
An example of a base query is:
SELECT SalesOrderNo FROM SalesOrder
The base query can be any valid SQL syntax that can include WHERE clauses, CASE statements etc.
From the Advanced Search screen click on Tools -> Base Query
This will open the following Base Query screen. You can click the Query Name drop down to view other previously created base queries or you can click ADD at the bottom of the screen to create a base query from scratch.
In the example below selected the previously created SalesOrder base query has been selected. This is a good way to analyze and learn the SQL statement previously created. Optionally, you can copy this existing SQL base query, modify it, and use it as your own custom base query.
In the example, a base query will be created from scratch and will not be using the previously created base query.
NOTE: Instead of manually typing out your base query it may be easier to create the base query directly in SQL Management Studio or another application where you can use an interface to develop a SQL statement.
In the example below ADD was clicked on the lower left of the Base Query screen to create a custom base query. A base query will be creating related to SalesOrder.
Query Name - Since the base query involves SalesOrder we will have entered the Query Name as SalesOrder -C (where the "-C" is used to signify to that the query is Custom). You can enter anything for the Query Name.
Module – Select the applicable module that query should belong to. Since the query is related to SalesOrder select SalesOrder as the module.
SQL Statement – Here you can type from scratch or copy and paste a valid SELECT SQL statement / query.
In the example below the following was entered:
SELECT SalesOrderID, SalesOrderNo, CustRefNo, SubTotal, UserCreated, DateCreated
FROM SalesOrder
Once the SQL statement has been entered, click the Verify button on the right of the screen to verify that the SQL statement is valid.
Click SAVE on the bottom left of the screen to save base query. Click CLOSE on the bottom right of the screen to exit the Base Query screen.
This should take you back to the Advanced Search – Search Designer screen.
|
