Query Subsystem

Query Header

When creating a new query, first enter a name and then select Standard Query in the Query Type window. This automatically launches the Query Header Definition window for defining the characteristics of the query. You can also edit the header information of an existing query by clicking the Header toolbar button on the Query Definition window.

Note:
When entering a new query object name, valid characters are: letters (A-Z, a-z); numbers (0-9); ~ (tilde); @ (at symbol); . (period); $ (dollar sign); _ (underscore); - (dash); + (plus sign). If an invalid character is used, a message will display.

Query Header Definition

The Query Header Definition consists of six categories, each with its own tabbed folder: File Info, Display, Options, Font/Color, Interfaces and User Aids. The design properties displayed on each of these tabbed folders are explained below.

This window consists of the following:

Panel

Name of the query.

Show Query+ Options

Check box to disable/enable options based on their Classic Query vs. Query+ availability.

File Info

 

Data Source

Options for defining the query's external data source:

Database

When this check box is selected, the information to be displayed will be from an external database table.

If not selected (Default), the information is assumed to be from a PxPlus data file.

Database Type

Select from the following types: ODBC, ODBC (Local), Oracle, Oracle (Local), DB2, DB2 (Local), MySQL, MySQL (Local), ADO, ADO (Local).

Database Name

Enter the database name (Fixed value or Expression).

Connect Option

Connection options to be appended to an OPEN statement when opening the database, such as PSWD=, USER=, etc. Multiple options are separated by ; (semi-colon).

File/Table

Name of the primary file or database table to be displayed in the query (Fixed value or Expression). Data can have embedded or external keys or can be sort files.

If your data source is a database, you must select a table name from the drop-down list. Otherwise, you can select a known data file from the Data Dictionary table lookup by clicking the Query Table View button (magnifying glass) to display a tree view of table names by Group. For information on creating a filter to locate a specific table name, see Filtering the Table Names Lookup.

Type the name of a PxPlus View or the pathname of a data file, or click the Browse button to look through the directory structure to find a file. When entering a pathname, if the file has an embedded dictionary, then the embedded dictionary will be used to determine column information. Otherwise, you must manually define the data fields to be used in the query. See Field Definition. See also Manual Field Definition below.

Key Information

For an External Database Table:

Click the Define Unique Sort Key button to display a window where you can define a table key based on fields in the table. The key definition will be added as a connection option on the OPEN statement using the KEY= clause.

For a PxPlus Data File:

Select the key (from drop-down selections) to sort the file in the query. (At run time, you can override this setting by loading an alternate key number into the %NOMADS'Query_Kno variable.)

Display

 

Title

(Not Applicable to Drop Query)

Text to appear on the title bar at run time. Click the drop-down arrow for a list of selections: Fixed, Expression, Message Library Reference.

Columns/Lines

Static Columns

(Classic Query Only)

Number of columns to remain fixed and kept on display when the user scrolls horizontally.

Display Line Height

Height of a query line in terms of lines high. Can accommodate larger font sizes. If set > 1 line, then the columns can word wrap.

You can also use $0A$ in formulas to break data over multiple lines, showing more information without horizontal scroll.

Center Text Vertically

(Query+ Only)

Select this check box to center text vertically in the query line only when Display Line Height is > 1.

Text that exceeds the column width is truncated and an ellipsis (...) is displayed.

When not selected (default), text is aligned to the top of the query line, and text that exceeds the column width is word wrapped.

Note:
When an image is used in the query, the setting is ignored and all text is centered vertically.

When the Show Query+ Options check box is not selected, the Center Text Vertically check box is disabled (not applicable).

(The Center Text Vertically option was added in PxPlus 2019.)

Position

Column and Line position of the top left corner of the panel. Not used if Panel Persistence is activated.

Absolute

Indicates exact position in columns/lines from the top left corner of the display.

Relative

Column/line position relative to main window.

Centered

Position centered relative to the screen (ignores column/line values).

Size

Width and Height of the panel. Not used if Panel Persistence is activated.

For Classic Queries, minimum width is 42 columns and height is 10 lines. The minimum Query+ panel is 80 columns by 18 lines.

When using Query+ Toolbar, Hybrid or Menu views (see Query View option), the panel dimensions can be modified by clicking the Test/Design toolbar button to display the query and dragging the edges of the query panel to the desired size. Click the Save + Exit button to save and incorporate these settings into the query definition when exiting the query.

(The ability to save query changes when in Test/Design mode was added in PxPlus 2023 Update 1.)

Grid Lines

Adds grid lines to the display. Click the drop-down arrow for a list of selections:

Default

Uses the value set in %NOMADS'Query_GridLines. Default is no lines.

None

No grid lines are displayed.

Full grid

Grid lines are displayed both between the columns and between each line in the output.

Vertical

Vertical grid lines are displayed between columns.

Horizontal

Horizontal grid lines are displayed between lines/rows.

(The Grid Lines option was added in PxPlus 2017.)

Display

Gray/White Display

Check box to display alternating gray and white backgrounds. Default (Off) indicates white only.

Row Highlight

(Query+ and Drop Query)

Add (Edit) row display option

Click the link to invoke the Row Display Option window that is used to assign visual attributes, such as bold text, text color or highlight color, to a row based on specified conditions.

(The Add (Edit) row display option was added in PxPlus 2021.)

Options

 

Query+ Options

The following options apply to the Query+ mode:

Query View

This option determines how the query will be displayed when assigned as a Panel to a Multi-Line. See Assigning a Query.

Note:
When the Use Classic Query option (see above) is selected, the Query View option is not available.

Click the drop-down arrow for a list of selections:

Default

The setting in %NOMADS'Query_View is used. If %NOMADS'Query_View is not set, the default will be the Toolbar view.

Drop Query

The Drop Query displays data in a Report View list box directly on the current panel. Drop queries load the entire dataset prior to display; therefore, it is advisable to prevent queries that are using large datasets from being displayed as Drop queries by setting this option to Toolbar, Menu or Hybrid.

Toolbar

Standard Query+ display featuring a toolbar to access features.

Menu

Features are accessed by a menu (no toolbar).

Hybrid

This view has an abridged toolbar with the remaining features available in the menu. The layout is somewhat similar to the Classic Query.

Drop Tree

The Drop Tree Query displays data in a Tree View list box directly on the current panel. Drop Tree queries are not recommended for large datasets, as the entire dataset is loaded prior to display. The Drop Tree supports only the Find, Refresh, Print, Filters and Profile features in its right-click popup menu.

In iNomads, you can also choose to Set the default Query View in the Template Configuration Misc tab.

All views require the same Query+ activation level. If not activated, then the query will display as a Classic Query.

(The Drop Query selection was added in PxPlus 2016.)
(The Query View option was added in PxPlus 2017.)
(The Menu selection was added in PxPlus 2017.)
(The Hybrid selection was added in PxPlus 2017.)
(The Drop Tree selection was added in PxPlus 2021.)

Auto Column Resize

Automatically adjusts the sizes of the columns to fit the width of the query.

Click the drop-down arrow for a list of selections: Default, Always, Never. If set to Default, the setting in %NOMADS'Query_AutoColSize is used.

(The Auto Column Resize option was added in PxPlus 2019.)

No Drop Query Header

Suppresses the display of the column headings in Drop Queries.

Click the drop-down arrow for a list of selections: Default, Always, Never. If set to Default, the setting in %NOMADS'Query_NoDropHeader is used.

(The No Drop Query Header option was added in PxPlus 2019.)

Use Classic Query

(Classic Query Not Applicable in iNomads)

Check box to force the query to display data using the Classic Query mode. This might be used when the query dataset is very large to avoid out-of-memory issues.

Note:
When the Use Classic Query option is selected, the Query View, Auto Column Resize and No Drop Query Header options are not available.

All Queries

The following options apply to all Query modes:

Auto Tab

Check box to pre-input a Tab character ($09$) so that when the query is exited, focus will automatically go to the next control in the tab sequence.

No Print Button

Check box to suppress the Print button at run time so that users cannot print reports. Default (Off) enables printing of a simple report showing title, file name, and column headings followed by a list of records that pass the selection criteria.

NOMADS automatically adjusts the font size to print all columns on the page. If the font appears too small for the page, you can select landscape orientation. You may need to change the column size and/or the number of fields in the query to have a design that fits.

Suppress Favorites

Suppresses the ability to select and display favorite records at run time.

Click the drop-down arrow for a list of selections: Default, Always, Never. If set to Default, the setting in %NOMADS'Query_Suppress_Favorites or %NOMADS'Query_Suppress_Popup$ is used.

Suppress Export

Suppresses the Copy and Export options normally available at run time.

Click the drop-down arrow for a list of selections: Default, Always, Never. If set to Default, the settings in %NOMADS'Query_Suppress_Export and %NOMADS'Query_Suppress_Popup$ are used.

Read as RECORD

(Available Only for Manually-Defined Files)

Forces NOMADS to read the file using READ RECORD, making it possible to read and display data from manually defined files that have fixed-length records that may contain binary data.

Classic Query Options

The following options apply to the Classic Query mode:

No Sort By

(Available when Use Classic Query check box is selected)

Check box to disable the Sort By drop box on a Classic Query. The file will be sorted using Key Information (File Info tab).

Sort by Columns
(Pre-load Data)

(Available when Use Classic Query check box is selected)

Check box to enable a Classic Query to be sorted by clicking the mouse on a column heading. Subsequent clicks toggle the sort between ascending/descending modes.

Default (Off) indicates sort by file keys. Database tables can only be displayed in Sort by Columns mode. PxPlus data files can display either sort by file keys or sort by columns.

Note: 
All data must be preloaded to process this option, possibly affecting performance. See Performance Considerations.

Switch Scroll Bar
for Large File

(Available when Use Classic Query check box is selected)

Check box to invoke alternate scroll bar logic to speed up access to large data files in a Classic Query. See Performance Considerations.

Used in conjunction with the %NOMADS'Query_Sbar_Max variable.

Use OK/Cancel Buttons

(Available when Use Classic Query check box is selected)

OK/Cancel buttons are added to the bottom of the run-time Classic Query panel, and the status bar is suppressed (query message is displayed to the left of the button). Selecting OK returns the value associated with the query record selected. (Query+ panels have Select/Close buttons.)

Start At Value

The Start At Value is the value that is passed to the query to determine which record in the query list will be selected when the query is initially loaded. This is done by matching the start value against record keys or return values as the file is read.

Two drop box options are available for defining the Start At Value: one to determine the case sensitivity of the Start At Value, and one to determine how the Start At Value is matched.

Case Sensitivity

Click the drop-down arrow for available selections: Case Sensitive (Default), Default to Uppercase and Default to Lowercase. By default, the comparison is Case Sensitive where the case of both the start value and key value (or return value) must match.

It is possible that the start value may be passed in with mixed case; however, the record keys may be all uppercase or lowercase. In these cases, you must set the case of the Start At Value to match that of the key values.

In the case where the starting value uses the matching option to match the return value, using either the Default to Uppercase or Default to Lowercase option will result in a case insensitive match.

Match Key Value/Return Value (Available for Query+ Only)

Click the drop-down arrow for available selections: Match key value (Default) and Match return value. By default, the starting value that is passed to the query is tested against the key that is being used to load the query. This is because the starting value generally matches the key value being used to load the query. In this case, the Match key value option is applicable.

However, there are instances where a query may be loaded using one key, such as a name or a description, but the return value is another key, such as a code or account number. In this case, use the Match return value option. Be aware that if there are duplicate return values, the match will occur with the first one encountered.

Note:
In the case where the return value is unrelated to the key by which the query is loaded, another way to set up the query is to load the query using the key that matches the return value. Use the Choose initial sort column link on the Query Definition window to set the column by which to sort the query when it is initially displayed.

(The "Match key value" and "Match return value" options were added in PxPlus 2023 Update 1.)

Return Value

Indicates the value to be returned when a record is selected. If omitted, the default is the value in the first column of the query.

This value can also be used to determine if lines are to be suppressed when duplicate return values are encountered. Use a PxPlus expression.

(The ability to suppress query lines based on duplicate return values was added in PxPlus 2021.)

No Return Value

Check box to ensure that the Return Value is not set when the query panel is exited.

When a query is invoked, you can pass a Start At value as an argument. When the query panel exits, the same argument is used for the Return Value. Rather than loading this argument with the value of the currently selected record, the argument is left unchanged. The value in a control with an attached query is not changed when the query ends.

Return Prime Key value in Hex

Check box to return the Hex value of the Primary key. This option applies only when using files with multi-segment keys and must be selected when adding a query to the Webster+ HTML page. See Step 5: Keys in the File Maintenance Generator.

When this check box is selected, the Return Value multi-line input displays HTA(Prime_key$) and is disabled.

When it is not selected, the Return Value multi-line input displays Default (first column) and is enabled.

(The Return Prime Key value in Hex option was added in PxPlus 2023 Update 1.)

Build Return Value

Click the link to invoke the Build Return Value window for defining the Return Value.

Values can be returned for a single field or multiple fields concatenated in a format you can parse after the value is returned:

   PROD_ID$ CST_SMN$+","+CST_NAME$(1,1)
   PAD(X1$,6)+PAD(X2$,12)+STR(LNK.AMT:"00000.00")
   SLS.FLD#1*100

You can also gain access to external keys using the special NOMADS query variable PRIME_KEY$ and return an entire record using the special NOMADS query variable ENTIRE_RECORD$:

   PRIME_KEY$+ENTIRE_RECORD$

Font/Color

 

Font Specification

Font

Select an existing font by name. Applies to screen presentation only. Fonts for printed reports are selected separately.

Size

Select from the list of available sizes (specific to the selected font) or enter a specific size. Positive sizes relate to the current font (1=Regular, 2=Double, .5=Half, etc.). Negative values are point sizes. Use in conjunction with the Display Line Height setting (see Display tab) to display larger fonts.

(Font support for Query+ was added in PxPlus 2021.)

Color

Background
Foreground

(For Classic Query) Select the background and foreground colors for the query panel label region (not the area where records are listed).

(For Query+) The background color applies to the panel background (the area below the records list), and the foreground color has no effect.

Click the Query button to access Color Selections. Valid formats for color selections include predefined system colors (e.g. Light Red), Custom (RGB codes), HTML Hex Color Codes, User Defined colors (e.g. Color17) and string Expressions.

(The Color Selections Query button and dialog were added in PxPlus 2020.)
(Background color support for Query+ was added in PxPlus 2021.)

Attributes

Check boxes to apply font attributes. Overrides Library and Panel defaults.

Available selections are Bold, Italics, and ANSI Characters. When the ANSI Characters check box is set to Off, you can use other character sets such as Wing Dings.

(Font attributes support for Query+ was added in PxPlus 2021.)

Theme

Assign a Theme to be applied to the query. The Theme can be defined as a Fixed value or string Expression.

Click the Theme Maintenance button to launch the Themes Maintenance utility for creating and editing themes.

Note:
A Theme specified at the Query level takes precedence over Themes set at the panel level, the Library level (Library Defaults) or the General level Theme set in %NOMADS'Theme$.

A Theme assigned to the %NOMADS'ThemeOverride$ property overrides all other Theme settings. See Applying a Theme to Your Application.

(Theme support was added in PxPlus 2019.)
(The Theme Maintenance button in Query Header Definition was added in PxPlus 2020.)

Interfaces

 

Initialization Procedure

Optional program to be called prior to opening the query panel. You may pass a comma-separated list of up to 20 optional parameters using the format: "program;label",arg$, arg$, arg$...

Closing Procedure

Optional program to be called upon exiting the query panel. You may pass a comma-separated list of up to 20 optional parameters using the format: "program;label",arg$, arg$, arg$...

Maintenance

Maintenance program for updating the file:

Library

Select a maintenance library name from the drop-down list, click the Browse button to look through the directory structure to find the library or type the library path. An expression can also be entered by preceding the expression with an = (equals sign); e.g. =libname$ or ="mylib.en".

Note:
The Library name may be a specific or generic reference. See Cascading Language Suffixes.

(Support for entering an expression for a Library name was added in PxPlus 2019.)

Panel

Select a file maintenance panel name from the drop-down list. If included, the user can edit the main data file by clicking the Edit File button at run time. The key of the currently selected record is passed to the maintenance program as ARG_1$, and the value returned in this argument is used to reposition the file.

In the Classic Query, if no records exist for display in the query at run time, the File Maintenance program is automatically invoked. If left blank, the button is suppressed when the query is run.

User Aids

 

Help Reference

(Classic Query Only)

Help text. Select from External or Internal Help types. At run time, Help is invoked by pressing Shift - F1.

Internal

Application supplied message text. Available selections are:

Fixed

Displays the text as entered.

Expression

Indicates that the Help file to be displayed is stored in a string variable or simple expression.

Message Lib Ref

Specify a Key associated with an existing message in the current message library.

External

Standard windows Help system consisting of a Help file name and an optional Keyword or Reference number (Fixed or Expression). Available selections are:

Fixed

Allows you to enter the name of a Windows Help file.

Keyword

Indicates that the text in the adjacent field is the index entry for the topic in the indicated Help file.

Reference

Indicates that the adjacent field contains the reference number for a specific topic in the context sensitive help.

Popup

Check box to indicate that the Help topic will be displayed as a popup rather than as an independent window.

Expression

Indicates that the Help file to be displayed is stored in a string variable or simple expression.

Test

Button used to test the Help display.

Message Bar

(Classic Query Only)

Message text to be displayed at the bottom of the panel. Available selections are:

Fixed

Displays text as entered.

Expression

Indicates that the Help file to be displayed is stored in a string variable or simple expression.

Message Lib Ref

Takes the message text from the indicated keyed file.

 

Note:
To set various Display, Options and Font/Color properties simultaneously on multiple query definitions in a library, see Query Bulk Edit.

Row Display Option

The Row Display Option window is used to assign visual attributes, such as bold text, text color or highlight color, to a row in the query data list. The various visual attributes can be assigned based on specified conditions being met.

To assign visual attributes to a column, see Query Column Display.

Note:
Display settings on individual columns will override Row Display settings.

Invoking Row Display Option

In Query Header Definition, on the Display tab, click the link Add (Edit) row display option (under the Row Highlight heading).

(The Row Display Option window was added in PxPlus 2021.)

This window consists of the following:

Display Method

The following methods are available to assign various visual attributes to a row:

None

No display method. (Default)

BoldText

Display text in bold font.

Colors

Set text and background colors.

HiLiteColor

Set background colors.

TextColor

Set text colors.

User

Specify your own display strings.

Parameter Values

Specify the query fields that are to be used as part of a condition.

To Add a Parameter:

Click the Add button (next to Parameter Values) and then click the drop-down arrow at the right of the newly-allotted parameter to select a field from the list, or define field characteristics (i.e. field number, offset, length) for manual fields.

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 rather than the actual name of the field.

To Remove a Parameter:

Select the parameter entry to be deleted and then click the Remove button (next to Parameter Values). To select multiple entries, use Shift-Click (consecutive selections) or Ctrl-Click (random selections).

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 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.

Note:
Selecting Default (i.e. no color) is a valid selection.

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.

Display strings are PxPlus expressions; therefore, select the Exp check box and enter the expression to use.

Example:

   '_COLOR'("Light Yellow")+'BB'

Note:
Be sure to use placeholder IDs rather than field name variables in your expression, if required.

You can also create your own display methods by creating your own 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.

Row Display Examples

Below are examples of setting up row display scenarios:

Example 1:

Your query has a BALANCE column.

Based on its value, you want the row to display using Red text if the value of BALANCE is negative and Green text if the value is 10000 or greater.

The remaining rows are to display using the default text color.

To do this:

Assign TextColor as the Display Method.

The conditions are based on the BALANCE column; therefore, select BALANCE from the drop-down list of Parameter Values. It is assigned an ID of %1.

Then create two Conditions:

   %1<0

   %1>=10000

For the first Condition, assign Light Red as the Color, and Dark Green for the second Condition.

Example 2:

Your query has a CUSTOMER_CLASS$ column.

You want to display a record in bold text if CUSTOMER_CLASS$ contains the value "VAR".

To do this:

Assign BoldText as the Display Method.

The condition is based on the CUSTOMER_CLASS$ column; therefore, select CUSTOMER_CLASS$ from the drop-down list of Parameter Values. It is assigned an ID of %1.

Then create the Condition:

   %1="VAR"

Build Return Value

The Build Return Value window is invoked by clicking the Build Return Value link on the Query Header Definition Options tab (under the Return Value heading). It is used for defining the Return Value (value to be returned when a record is selected).

Note:
When working in this window, you cannot use external keys or functions in your expression. They must be entered directly into the Return Value input field.

This window consists of the following:

Field Definition

File

Name of the file or table to be used in building the return value. Click the drop-down arrow for a list of the main and link files used in the query. If the file is not maintained in the data dictionary, you must define the data fields manually. See Manual Field Definition below, as well as Field Definition.

Elements

List of elements for the selected file.

Selected

List of elements that comprise the return value.

Add

Adds an item from the Elements list to the Selected list.

Remove

Removes a Selected item.

Manual Field Definition

Field No.

Ordinal position/sequence number of the field to be displayed in this column. The first field is number 1. If your manual file has an external key, define it as field #0 (zero).

Offset
Size

Use Offset and Size to define sub-strings. The first character of a string has an Offset of 1. Size indicates the number of characters in the sub-string.

Type

Select a field type: String or Numeric.

Add

Adds the manual field definition to the Selected list.