Query Definition |
|
Query contents are defined in the Query Definition window. This window is used to set up link files, define columns, edit the Query Header and set up selection criteria. After the query is defined, it can be tested and saved.
The Query Definition window launches after defining the Query Header for a new query or when selecting an existing query object in the Library Object Selection window. It can also be accessed when using the NOMADS Panel Designer to assign a query to a List Box, Drop Box, Multi-Line or Grid control. See Assigning a Query.
The Query Definition window consists of the following:
File Elements |
A list of files (the main data file/table and any defined link files) and their elements. Icons next to each element indicate whether the field is a string, numeric or date (i.e. DATE class) field. (Manual files have no elements listed.) Array elements are displayed with their dimensions in braces; e.g. Month${1:12}. Items can be selected from this list for addition to the Columns list for display in the query. Select an item by double-clicking on it, by highlighting it and clicking a selection button, or by selecting the Add option from the right-click popup menu associated with the item. Popup menus associated with the main file and cross-reference files also allow you to add fields from the respective files. When an array is added as part of an "add all fields" or "add all fields in file" scenario, all the elements in the array are automatically added to the Columns list. If the array alone is selected from the File Elements list, the Select Array Element window is displayed to allow you to select all elements or select a single array element by specifying its subscripts. This window consists of the following:
(Support for array elements in the query was added in PxPlus 2022.) | ||||||||||||
A list of columns selected from the File Elements list. Columns can be a simple element (i.e. field/column name such as InvoiceNumber) or a formula (i.e. a PxPlus expression). When a file element is initially added to the Columns list, default characteristics such as title, column width, format, etc. are set. These can be modified later using the Column Options window or by double-clicking the column item. | |||||||||||||
Selection Buttons |
The selection buttons between the lists are used to add all or only selected items from the File Elements list to the Columns list. They are also used to remove all or only selected items from the Columns list. For manual files, select a file and click the selection button to invoke the Column Options window where you can manually define the column to add. | ||||||||||||
Select as 'Hidden' |
When this check box option is selected, any items selected from the File Elements list are added to the Columns list as initially hidden; i.e. the column is initially hidden at run time until the user selects it to be shown. This option can also be toggled for a specific element by right clicking on the element in the Columns list and selecting the Hide/Show option or by using the Column Options window. | ||||||||||||
Choose initial sort column |
(Query+, Not Applicable to Drop Tree Query) Click the link to invoke the Select Column Sort Order window to specify a column to sort by (ascending or descending) after the query is loaded. This overrides the default sort sequence, which is determined by the key selection in the query header. The column sort affects the initial load only; subsequent reloads will maintain the last column sort used in the query. This window consists of the following:
Note:
| ||||||||||||
Move Up |
Buttons (below the Columns list) that are used to change the order of the items in the Columns list. | ||||||||||||
Close |
Exits the Query Definition window. If any unsaved changes are detected, you will be prompted to save the changes. (The Close button was added in PxPlus 2019 Update 1.) |
The Query Definition toolbar consists of the following functions:
Save |
Saves the current query definition. (If the session terminates abnormally without saving, the unsaved changes can be recovered the next time you load the definition.) |
Test |
Tests the current query definition. |
Header |
Launches the Query Header Definition window for editing header information. |
Filter |
Launches the Query Selection Criteria window for setting up selection criteria. |
Security |
Launches the Object Security Definition window for setting up top-level security for the query. See Query Security. (The Security button was added in PxPlus 2021.) |
|
Prints the details of the query definition. |
Add Link |
Launches the Query File Link window for adding, modifying and removing a link to a cross-reference file. |
Column Options |
Launches the Columns Options window for defining the column title, width, format, etc., as well as the Field Definition for manually defined files. |
Add Formula |
Launches the Query Formula Definition window for adding or modifying a formula. |
(Available when Show Query+ Options check box AND one or more Columns are selected) Launches the Chart Wizard for defining simple charts based on the columns of a query. The results are public developer AutoChart definitions that can be used to generate and load data into a Smart Chart. See Smart Controls. (The Define Chart button was added in PxPlus 2019.) | |
Show Query+ Options |
When selected, only query options available to Query+ displays are enabled. In addition, the Test button will generate a Query+ panel. If not selected, options and test display will be those of the Classic Query. For information on the modes of query display, see Query Subsystem. Note: |
The Query Definition menu bar consists of a Projects menu with the following selections:
Projects |
Lists options for projects:
| ||||
Quit |
Closes the Query Definition window. If any unsaved changes are detected, you are prompted to save the changes. |
Query File Link allows additional data to be retrieved from associated cross-reference data files or database tables. For example, if the Customer data file contains a classification code, but the associated class description has to be retrieved from a ClassInfo data file, you can define the ClassInfo file as a link file using the classification code from the Customer data file as its key.
To launch the Query File Link window, click the Add Link button on the Query Definition toolbar to define a new link. To modify or remove an existing link, highlight the link file in the File Elements list and then select either the Update Link button or the Remove Link button on the Query Definition toolbar.
The Query File Link window displays a list of all currently defined links, if any, for the selected query. Link definitions can be added, modified or removed.
A link definition consists of the following:
(Select a Link Definition) |
Click this button (in the first column) to access the Select a File Link Definition window for choosing an existing file link definition. The selected definition is loaded into the list and can be adjusted if desired. (File link definitions are defined in File Link Maintenance.) Alternatively, you can select the Pre-Defined File Links button in the bottom left corner. |
Expr |
Select this check box if the entry for Link To File/Table is an expression. |
Link To File/Table |
Name of the link file/table. This can be defined as a fixed value or an expression (see Expr above), which is entered or selected from the drop-down list of existing files/tables: |
Access Key |
Key to be used to read a link file. Select a key from the drop-down list. |
Key Expression |
PxPlus expression consisting of field(s) from the main file (or other link files) and literals to build the key value to read the link file. Examples: TRN_CUSTID$ "D"+FLD#2$(1,3) PAD(Company$,5,$00$)+PAD(Department$,10,$00$)+EmployeeID$ KEY(__fileFN,KNO=0,KEY=Company$:Department$:EmployeeID$) When dealing with multi-segmented keys (i.e. keys made up of more than one field), you can use a + (plus sign) to concatenate multiple segments when entering a free-form expression. Be sure to add the correct padding logic to the initial key segments. By default, the initial segments of native PxPlus keys are padded with $00$ characters, and the final segment is not padded. Such a key definition might look like this: PAD(Company$,5,$00$)+PAD(Department$,10,$00$)+EmployeeID$ Individual applications can pad key segments with other characters, such as spaces; therefore, knowledge of the key architecture of individual files is a must. Knowledge of segment characteristics is not necessary; however, if the key expression uses the KEY(__linkFN,KNO=n,KEY=FLD1$:FLD2$) format where __linkFN is a special channel variable used by the system when evaluating the key, n is the key number used by the file, and FLD1$:FLD2$ are the key segment variables. The latter format is also more flexible as it can handle changes in key segment length if keys are updated for a file. An easy way to build a key expression is to use the key builder utility. This is invoked by clicking the dotted button in the cell associated with this option. The key expression is built by specifying key segments to match the target key definition. These segments can consist of fields from the parent file, literal values and variables. Partial items can be defined, and segments can be padded, have characters stripped, or be converted to upper/lower case. In the case of multi-segment keys, a Generate a KEY=F1$:F2$ expression check box option is available: • If it is not selected, the key segments will be concatenated, and individual key segments would have to be padded or manipulated appropriately. • If it is selected, then a key expression in the format KEY(__linkFN,KNO=n,KEY=FLD1$:FLD2$) will be generated where the segments need not be manipulated. (For an explanation of the contents of the generated key, see Multi-Segment Keys.) (The Generate a KEY=F1$:F2$ expression check box was added in PxPlus 2023.) |
Record Prefix |
Prefix to affix to field names to identify the file to which they belong. |
Read Record |
(Valid Only for Native Files with No Embedded Dictionary) Select this check box if the file is to be read as an unparsed record. |
Connect Options |
Connection options for opening an external database table. |
Click the dotted button associated with this option to launch the Object Security Definition window for setting up link file level security for the query. See Query Security. (The Security column was added in PxPlus 2021.) | |
Pre-Defined File Links |
Click this button to access the Select a File Link Definition window for choosing an existing file link definition. The selected definition is loaded into the list and can be adjusted if desired. (File link definitions are defined in File Link Maintenance.) Alternatively, you can select the link button in the first column of the grid. (The Pre-Defined File Links button was added in PxPlus 2021 Update 1.) |
The sequence of link files/tables in the list defines the sequence in which they are to be read. If you have multiple levels of link files, for example, the main file links to File_A, which links to File_B, then File_A should be read before File_B and thus precede it in the list. To rearrange the order, use the Move Up/Move Down buttons.
Changes that are applied in this window will be applied to the File Elements and Columns lists on the main Query Definition window. The exception to this is that columns belonging to deleted links are not removed from formulas.
For a Data Dictionary/View Definition/Database Table
When you select an item from the File Elements list in the Query Definition window, it is placed in the Columns list with default values for its title, format width and query derived from the data dictionary, view or table definition.
To change the values for a selected element, launch the Column Options window by using one of the following methods:
• Click the Column Options button on the Query Definition toolbar.
• Double-click on the element.
• Right click on the element and then select Update column properties from the popup menu.
For a Manually-Defined File
When you select a manually-defined file from the File Elements list in the Query Definition window and click the selection button, the Column Options window is presented for defining the data element in terms of field number, offset, size and data type. See
Field Definition and
Manual Field Definition.
The Column Options window consists of the following:
(Only Available for Manually-Defined Files)
| |||||||||||
Title |
Heading that users will see at the top of the column at run time (either a Fixed value, string Expression or Message Library Reference). | ||||||||||
Width |
Column width in columns. This can be adjusted as necessary, depending on the selected font. | ||||||||||
(Query+ and Drop Query) Alignment of the value within the column. Settings include Default, Left, Center and Right justification. The Default setting for text values is Left justification and Right justification for numeric values. Note:
| |||||||||||
Format |
Can be either Mask or Program.
| ||||||||||
Display Option |
Click the Define button to launch the Query Column Display window where you can assign visual attributes, such as bold text, text color, highlight color, or an image, to a column. See Query Column Display below. | ||||||||||
Sort Options |
(String Only, Query+, Drop Query and Classic Query with Sort by Column Option) The following sort options can be assigned to a column: Sort As Is; Use Sort Algorithm; Date Sort.
| ||||||||||
'Tip' Values |
(Numeric Only, Query+, Drop Query and Classic Query with Sort by Column Option) (Optional) Tip values to be displayed when the mouse is placed over the column heading. Values include Total, Average, Minimum and Maximum. | ||||||||||
(Query+ and Drop Query) Logic to execute when the column is double-clicked. Click the drop-down arrow for a list of event actions: Ignore, Link, Call and Execute. See Actions and Parameters. The specified logic overrides selecting the query record. Columns with a hotlink are displayed in the color defined by the 'OPTION'("StdLvueHotlinkClr",color$) setting and are underlined while hovering over the column. See OPTION mnemonic.
(The Hotlink Logic option was added in PxPlus 2018.) | |||||||||||
Query |
(Classic Query Only) Optional sub-query assigned to the column.
At run time, users can invoke the sub-query by highlighting the column title and clicking the enabled query button on the toolbar. The current contents of the query column are passed as the Start At Value for the sub-query. (Support for entering an expression for a Library name was added in PxPlus 2019.) | ||||||||||
Initially Hide Column |
Select this option to initially hide the column at run time until the user selects it to be shown. | ||||||||||
Launches the Object Security Definition window for setting up column level security for the query. See Query Security. (The Security button was added in PxPlus 2021.) |
A formula is a PxPlus expression (numeric or string) consisting of any combination of variables, constants and functions, whose resulting value is displayed in a query column. Formulas that have been assigned a name can be used within other formulas, noting that the order of evaluation of formulas corresponds to the order in which they are defined in the query definition.
To launch the Query Formula Definition window, click the Add Formula button on the Query Definition toolbar.
This window consists of the following:
(Optional) Enter a formula name to easily identify the formula throughout the Query Subsystem: • For new formulas, the formula name will be pre-loaded with FML_QueryName_N (where QueryName is the name of the query and N is a numeric identifier), which can be changed or deleted. • For existing formulas, the formula name will be blank until it is filled in. All formula names will be prefixed with FML_ and must adhere to the rules for naming variables. The first character must be alphabetic (A-Z or a-z), the remaining characters can be alphabetic, numeric (0-9), . (a period), or _ (an underscore). Naming a formula allows you to: • Use the evaluated result of the formula in another formula. Note:
| |||||||||||
Column Title |
Column heading that users will see at the top of the formula column at run time; e.g. Customer Name. Can be a Fixed value, string Expression, or Message Library Reference. | ||||||||||
Formula |
PxPlus expression (numeric or string) consisting of any combination of variables, constants and functions. In formulas, you can refer to an external key as PRIME_KEY$. | ||||||||||
Width |
Column width in columns. This can be adjusted as necessary, depending on the selected font. | ||||||||||
Alignment |
(Query+ and Drop Query) Alignment of the value within the column. Settings include Default, Left, Center and Right justification. The Default setting for text values is Left justification and Right justification for numeric values. Note:
| ||||||||||
Format |
Can be either Mask or Program.
Note: | ||||||||||
Click the Define button to launch the Query Column Display window where you can assign visual attributes, such as bold text, text color, highlight color, or an image, to the column. See Query Column Display below. | |||||||||||
Sort Options |
(String Only, Query+, Drop Query and Classic Query with Sort by Column Option) The following sort options can be assigned to a column: Sort As Is, Use Sort Algorithm, Date Sort.
| ||||||||||
'Tip' Values |
(Numeric Only, Query+, Drop Query and Classic Query with Sort by Column Option) (Optional) Tip values to be displayed when the mouse is placed over the column heading. Values include Total, Average, Minimum and Maximum. | ||||||||||
Hotlink Logic |
(Query+ and Drop Query) Logic to execute when the column is double-clicked. Click the drop-down arrow for a list of event actions: Ignore, Link, Call and Execute. See Actions and Parameters. The specified logic overrides selecting the query record. Columns with a hotlink are displayed in the color defined by the 'OPTION'("StdLvueHotlinkClr",color$) setting and are underlined while hovering over the column. See OPTION mnemonic.
(The Hotlink Logic option was added in PxPlus 2018.) | ||||||||||
Initially Hide Column |
Selecting this check box initially hides the formula column at run time until the user selects it to be shown. | ||||||||||
Launches the Object Security Definition window for setting up column level security for the query. See Query Security. (The Security button was added in PxPlus 2021.) |
The Query Column Display window is used to assign visual attributes, such as bold text, text color, highlight color, percent bars, or an image, to a query column. The various visual attributes can be assigned based on specified conditions being met.
To assign visual attributes to a row in the query data list, see Row Display Option.
Invoking Query Column Display
Follow these steps:
|
1. |
In the Query Definition window, click on a column in the Columns list and then select the Column Options toolbar button. |
|
2. |
In the Column Options window, click the Define button (next to Method under the Display Option heading). |
Alternatively, invoke the Query Column Display window by clicking the Define button in the Query Formula Definition window when adding or modifying a formula.
This window consists of the following:
Display Value |
Value that will be displayed in the column. (The exception is if an image is to be displayed instead.) | ||||||||||||||||||
The following methods are available to assign various visual attributes to a column:
Note:
| |||||||||||||||||||
Parameter Values |
In addition to the Display Value, which is the default parameter value, you can specify other query fields as parameters if they are to be used as part of a condition or display string. Formulas must be named to be included as additional parameters. See Query Formula Definition. Each parameter is assigned an ID (such as %1, %2, etc.) that is to be used in a condition and display string expressions rather than the actual name of the field. Prior to deleting, a message will display. | ||||||||||||||||||
Condition |
Set conditions for the assigned visual attribute(s). Conditions consist of PxPlus expressions that can be evaluated to zero/non-zero, but using the ID placeholder in the place of a field name; e.g. %1="Y". To Add a Condition: Click the Add button (below Condition grid) and then enter the condition. Multiple conditions can be defined, each with its own assigned display setting (i.e. color, image, etc.). Conditions are evaluated in the order in which they appear until a condition evaluates as true (i.e. non-zero). This means that if two conditions are set, %1<10 and %1<100, then values less than 10 will be assigned the first display setting, and values from 10 to less than 100 will be assigned the second display setting. Values that fall outside of the range of the specified conditions may or may not have an assigned display setting. To assign a setting to these values, simply add a blank condition at the end and assign a display setting. Otherwise, no display setting will be assigned, and default text and color will be used for these values. Use the Move Up/Move Down buttons (below Condition grid) to change the order of the conditions. To Remove a Condition: Select the condition entry to be deleted and then click the Remove button (below Condition grid). To select multiple entries, use Shift-Click (consecutive selections) or Ctrl-Click (random selections). Prior to deleting, a message will display. | ||||||||||||||||||
Color |
(Display Method is Colors, HiLiteColor, PercentBar, PercentBarWithLabel or TextColor) To assign a color to a condition, click the dotted button at the right of the Color cell to launch the Color Selections window. In the case of the PercentBar and PercentBarWithLabel methods, the assigned Bar Color will be the color of the top of the bar with an optional Gradient Color for the bottom. (If a gradient color is omitted, the bottom of the bar will be shaded.) Note:
| ||||||||||||||||||
Max Value |
(Display Method is PercentBar or PercentBarWithLabel) By default, a column value of 100 causes PercentBar to fill the entire width of the column, a column value of 50 fills half of the column width, and a column value of 0 (zero) displays no bar. (Max Value was added in PxPlus 2017.) | ||||||||||||||||||
Image |
(Display Method is Image) To assign an image to a condition, click the dotted button at the right of the Image cell to launch the Bitmaps window to select a particular bitmap. You can also specify an expression by selecting the Exp check box and entering an expression using placeholder IDs. | ||||||||||||||||||
Display String |
(Display Method is User) Display strings allow greater flexibility when assigning display attributes for particular conditions; e.g. you can mix bold text and background color. Note: |
You can also create your own display methods by creating your own column display class. The existing methods are in the *obj/qrycoldsp class. Create your own class and inherit the *obj/qrycoldsp class in yours by using LIKE "*obj/qrycoldsp" in its definition. Then incorporate your new methods. Set %NOMADS'Query_ColumnDisplay$ to the name of your class, and your new methods will become available.
Below are examples of setting up column display scenarios:
Description |
Method |
Example 1: For the BALANCE column, you want to display negative values using Red text and high values (10000 or greater) using Green text. |
To do this: %1<0 For the first Condition, assign Light Red as the Color, and Dark Green for the second Condition. |
Example 2: Your query has the following columns: |
To do this: %2="VAR" |
Example 3: You want to highlight the entire CLIENTID$ column with a Light Green custom background color. |
To do this: Leave the Condition blank. Select your custom color in the Color Selections window. |
Example 4: Given a salesperson's yearly sales and sales target, you want to display a PercentBarWithLabel showing the percentage of the target that was achieved by each salesperson. |
To do this: 100*SMN_YR_SALES/SMN_YR_TARGET Set a format for the formula such as ##0.0%. Click the Define button to select a Display Option. For the Display Method, select PercentBarWithLabel. (Example 4 using the PercentBarWithLabel method was added in PxPlus 2017.) |