jBASE Query Language

The jBASE Query Language (jQL) is a powerful and easy to use facility, which allows you to retrieve data from the database in a structured order and to present the data in a flexible and easily understood format. You can enter jQL commands from your terminal or embed jQL commands in applications programs, procs and paragraphs to access data in Direct Connect files. The language is characterized by the use of intuitive commands that resemble everyday English language commands.

You might for instance manage a retail department and need to review a particular set of figures, which requires the phrase: “Show me the sales figures for January sorted in date order.”

The jQL Command would look like this:

LIST SALES WITH MONTH = “JANUARY” BY DATE

By using the jQL command LIST with a file named SALES and your predefined data definition records such as MONTH and DATE, you can construct complex ad-hoc reports directly from the command line interface (>). You can also choose how you want the information presented, displayed directly to your printer or to your screen, listed in date order, or in descending or ascending order. The choice is yours as jQL contains a rich range of commands for listing, sorting, selecting and controlling the presentation of your details and is a safe language for end users.

With the exception of the “EDELETE” command, jQL will not alter the contents of the source data files. All jQL command sentences begin with a verb-like command such as LIST or SELECT followed by a file name such as SALES or PERSONNEL, and then a series of qualifiers and modifiers with which you control elements such as eligible data, report formatting, any totals that you want to appear and so on.

Most data files on the system will have two assigned storage areas: for the data (the data section) and for the data definition records (the dictionary section).

Some files might be single level and others might have multiple data sections (see the File Management chapter of the System Administrators Guide for more details).

Data definition records kept in the dictionary portion of the file defines all the data fields in a file. These data definition records do not have to exist (you can use defaults provided in the environment variables or even the dictionaries of other files). However, where you need to manipulate ‘say’ dates (which are held in internal format), or to join data held in different files, you will find that one or more definition records will be required for each data field. The data definition records are simple to create and maintain.

Example

Data definition records (or DICT records) allow you to specify the position of the data in a record (its field number).

• A narrative to be used as a column heading.

• Any input or output conversions required (such as for dates).

• The data type (left or right justified, or text that will break on word boundaries) and

• A column width, used in reports.

Input and output conversion codes can also be used to manipulate the data by performing mathematical functions, concatenating fields, or by extracting specific data from the field.

jBASE uses a three-dimensional file structure called a non-first normal form data model to store multiple values for a field in a single record known as multivalued fields. A multivalued field holds data that would otherwise be scattered among several interrelated files.

Two or more multivalued fields can be associated with each other when defined in the file dictionary. Such associations are useful in situations where a group of multivalued fields forms an array or are a nested table within a file. You can define multivalued fields as belonging to associations in which the first value in one multivalued field relates to the first value in each of the other multivalued fields in the association, the second value relates to all the other second values. Each multivalued field can be further divided into sub values, again obeying any relationships between fields.

A jQL command sentence is entered at the shell in response to a command prompt (:) or a select prompt (>). If a command such as SELECT or GET-LIST creates an implicit list while in jSHELL, it displays the select prompt. Each sentence must start with a jQL command and can be of any length. Press <ENTER> to submit the constructed sentence. If you enter an invalid command, the system will reject it and display an appropriate error message.

Example

jsh ~ -->SORT jcustomers FIRSTNAME LASTNAME CITY STATE NUMUSERS WITH FIRSTNAME = "TED" AND NUMUSERS > “10” BY CITY DBL-SPC HDR-SUPP (P

The verb in this case is SORT. The file specifier is jcustomers. The fields specified in the output specification are FIRSTNAME, LASTNAME, CITY, STATE and NUMUSERS. The selection criteria specify that only those records with a FIRSTNAME of TED and with more than 10 users should be returned. The sort criterion says to order the results by the CITY field. The format specifier sets the output to be double-spaced with no header. The (P option sends all output to the printer rather than the screen.

When you are typing words in response to the TCL prompt, the system allows you to enter up to 240 characters before it performs an automatic linefeed. You can extend a line by entering the line continuation characters. To enter the continuation sequence hold down the CTRL key and press the underscore key (_), which may require you to hold down the shift key. Follow this combination immediately with the RETURN key.

Use the following words and symbols as described in this section as all have special significance within a jQL sentence. These words are defined in each Master Dictionary (MD) and their definitions should not be changed.

A jQL command sentence must contain at least a verb and a file name. The verb specifies which process to perform and the filename indicates the initial data source.

You can add optional clauses to refine the basic command. You can use clauses to control the range of eligible record keys, define selection and sorting criteria, or to specify the format of the output, and so on.

Only a verb and filename are required. The following list summarizes each element in the syntax.

Command Syntax

jQL-verb {DICT} file-specifier  {field-list} {record-list} {selection-criteria} {FROM #}{sort-criteria} {USING file- specifier} {macro-call}{output-specification} {format-specification} {output-limiter}{(options}

Syntax Elements

"A" codes provide many powerful features, which include arithmetic, relational, logical, and concatenation operators, the ability to reference fields by name or FMC, the capability to use other data definition records as functions that return a value, and the ability to modify report data by using format codes.

The A code also allows you to handle the data recursively, or “nest” one A code expression inside another.

Syntax Summary

The A code function uses an algebraic format. There are two forms of the A code:

• A uses only the integer parts of stored numbers unless a scaling factor is included.

• AE handles extended numbers. Uses both integer and fractional parts of stored numbers.

Command Syntax

A{n}{;expression}
 AE;expression

Syntax Elements

• n is a number from 1 to 6 that specifies the required scaling factor.

• expression holds operands, operators, conditional statements, and special functions.

AE;expression Format

The AE format uses both the integer and fractional parts of stored numbers. Use format codes to scale the output.

Examples of Numeric Results

Data Record		A Code
Field 1	Field 2	A;1 + 2	A3;1 + 2	AE;1 + 2
4	012	16	16000	16
-77	-22	-99	-99000	-99
0.12	22.09	22	22210	22.21
-1.234	-12.34	-13	-13574	-13.574
-1.234	123.45	122	122216	122.216

It does not allows input conversion.

An;expression Format

The "An;expression" format performs the functions specified in expression on values stored with an embedded decimal point. It then converts the resulting value to a scaled integer.

An Format

The "An" format converts a value stored with an embedded decimal point to a scaled integer. The stored value’s explicit or implied decimal point is moved n digits to the right with zeros added if necessary. Returns only the integer portion.

Field 2 of the data definition record must contain the FMC of the field that contains the data to be processed.

A: Expression Format

Performs the functions specified in expression on values stored without an embedded decimal point.

The following sentence lists information about ORDER numbered 200 to 399.

LIST ORDER => ‘200’ AND < ‘400’

The following sentence results in a report listing customer information only about customer 40823 because in the absence of a relational operator, it assumes an equal (=). The only customer number greater than 40823 and equal to 40825 is 40825.

LIST CUSTOMER > ‘40823’ AND ‘40825’

The following sentences do not list information regarding 117 and 119 because they are not on the implicit list. Although this sentence seems to have an explicit item-id list and an item-id selection clause, the whole series is a selection clause because there is a relational operator somewhere in the list.

SELECT ORDER GT ‘200’
42 RECORDS SELECTED

The arithmetic F code operators work on the top stack entry or the top two stack entries. They are:

Arithmetic operators are:

This provides interface for jBASIC subroutines or C functions to manipulate data during jQL processing. Synonymous with CALL code. See Calling Subroutines from Dictionary Items for more details.

The BETWEEN connective followed by two value strings is a shorthand way of saying ‘all values greater than the first value string and less than the second’. The value of the second value string must be greater than the value of the first to select items. Value strings including special characters ^, [ and ] are not valid.

The BREAK-ON connective causes monitoring of the following fields for change permitting up to fifteen breaks within one sentence treated in hierarchical left to right order. The first BREAK-ON in the sentence is the highest level.

When detected, the change in the value of the field outputs a blank line, followed by a line with three asterisks, and then another blank line. If the BREAK-ON clause specifies text, then it outputs the text in place of asterisks. If the text is wider than the column width, the processor applies the same justification as the named field.

You can suppress the BREAK-ON output by setting the column width of the field to zero. You can use BREAK-ON in conjunction with the TOTAL connective to generate subtotals. If using the modifier DET-SUPP with TOTAL and BREAK-ON, it displays only the subtotal and grand total lines.

The table below shows the BREAK-ON options:

Controlling and dependent fields provide a method for creating sub lists from records.

A controlling field is one, which has the code D1 in field 8 of its data definition record and points

to its controlling field.

When the system finds a controlling field, it will:

1. Look for the first field specified in the output specification clause that matches each FMC (Field Mark Count) of its dependent field and has D2 code in field 8 of the data definition item specifying the controlling field.

2. Position the found fields in the order found to the immediate right of the controlling field for display.

3. Display an asterisk (*) under the column heading of each found field.

4. Dependent fields are output immediately to the right of their controlling field regardless of the order in which you specify them.

5. An independent field found between the controlling and dependent fields is moved “logically” to the right of the controlling and dependent fields.

6. Ignore dependent fields unless you specify the controlling field.

Example

SORT SALES P.CODE S.CODE =”ABCORDER ORD.ID ORD.QTY = “5””

Selects all the records in the ORDER file and outputs the ORD.ID data. The ORD.QTY data will only be included if it matches 5. Any other value will be shown as blank.

SORT ORDER BY ORD.QTY BREAK-ON ORD.QTY ORD.ID

Selects all the records in the SALESORDER file in ORD.QTY order and outputs a line for each record until the ORD.QTY changes. At this point, a control break triggers and outputs the running total of ORD.QTY. At the end of the report, it displays a cumulative total for ORD.ID.

Using report qualifiers, you can tailor the layout of the entire report by setting up headers and footers on each page, adjusting margins and spacing, and determining output orientation (horizontal or vertical). In addition, there are two jQL commands LIST-LABEL and SORT-LABEL, which enable you to format and sort mailing labels.

This command retrieves selected records and generates a list composed of data fields from those records as specified by any explicit or default output specifications. Each sub value within a field becomes a separate entry within the list.

Command Syntax

BSELECT file-specifier {record-list} {selection-criteria} {sort-criteria} {USING file-specifier}{output-specification} {(options}

When the command terminates, it displays the total number of entries in the generated list and makes the list available as if generated by a SELECT, GET-LIST or other list-providing command.

If you do not specify a sort-criteria clause, the record list will be unsorted and if you do not specify an output-specification, then it uses the default data definitions “1”, “2”, etc.

Example

BSELECT ORDER WITH ORD.QTY = “500]” ORD.AMT

Creates a list containing all ORD.QTY values from all the records in the ORDER file, which have an ORD.QTY that CONTAINS ORDERS = 500.

Concatenates fields, literals, and the results of a previous operation. The command syntax is:

C{;}n{xn}...

Syntax Elements

• ; – It is optional and has no function other than to provide compatibility.

• x – The character for insertion between the concatenated elements. If you specify a semicolon (;), no separator will be used. Any non-numeric character except system delimiters (value, sub value, field, start buffer, and segment marks) is valid.

• n – It can be any one of the following:

  • A literal enclosed in single quotes, double quotes, or backslashes.

  • An asterisk (*) to specify the last generated value of a previous operation.

See the descriptions of the function codes (A, F, FS and their variants) for other concatenation methods. This conversion does not invert and applies the concatenation to the input data.

Examples

C1;2

Concatenates the contents of field 1 with field 2, with no intervening separator character.

C1*2

Concatenates the contents of field 1 with an asterisk (*) and then the content of field 2.

C1*”ABC” 2/3

Concatenates the contents of field 1 with an asterisk (*), the string ABC, a space, field 2, a forward slash (/) and then field 3.

The syntax for calling a subroutine from a dictionary item is:

B; {filename} subname 

or

CALL {filename} subname 

Where,

• filename is ignored but provided for compatibility with older systems.

• subname is the name of the called subroutine (or function). This subroutine must reside in one of the libraries defined by the user.

The subroutine can be called as a conversion (attribute 7 of the dictionary item) or as a correlative (attribute 8 of the dictionary item). Data is passed to and from the subroutine with named COMMON elements. In each subroutine, the following line must be included:

INCLUDE /usr/jbc/include/qbasiccommonpick (Unix)

or

INCLUDE \jbase30\include\qbasiccommonpick (Windows)

For ex-Sequoia users, you may INCLUDE the file qbasiccommonseq, which provides compatibility with that platform. The INCLUDE file defines the named common that is used by jQL. The named common consists of two arrays access and newpick.

access

access(1)	Data file open variable
access(2)	Dictionary file open variable
access(3)	Data item currently being processed. If BY-EXP is used then only the current multi-value from each attribute is included in the item.
access(4)	Item count  (Initialized to 1 at the start of each jQL statement)
access(5)	Attribute being processed. This is the value in attribute 2 of the calling dictionary item.
access(6)	Value mark counter
access(7)	Sub value mark counter
access(8)	reserved
access(9)	reserved
access(10)	Item id
access(11)	Data file name
access(12)	reserved
access(13)	reserved
access(14)	Multivalue number from an exploded select-list
access(15)	reserved
access(16)	reserved
access(17)	reserved

By default, jBASE will only call a subroutine once per item. This is normally desirable, since value and sub value manipulation can be done within the subroutine. In addition, it is clearly more efficient to only call the subroutine once per item. However, for backward compatibility, jBASE can be configured to call the subroutine for every value and sub value processed. If this is required then set jql_mv_subcall = true in usr/jbc/Config_EMULATE. If this setting is in place, access(6) and access(7) are incremented appropriately as each value and sub value is processed. Otherwise, the values in access(6) and access(7) have no meaning.

newpick

newpick(1) through newpick(11) – reserved

newpick(12) - On entry to the subroutine, this will contain the value of the data passed from jQL to the subroutine. By default, this will be all the data defined by the calling dictionary item (all values and sub values). However, if jql_mv_subcall = true is set, then the subroutine is called for every value or sub value and newpick(12) contains just each value or sub value as it is processed.

It is worth noting that a subroutine can be called as part of a multi-valued correlative. For example, the calling dictionary item could look like:

<1>S
<2>17
<8>F;"ABCD"]CALL SUB1

In this instance, the data defined by the calling dictionary item is "ABCD". However, if the calling dictionary item is:

<1>S
<2>17
<8>CALL SUB1

The data passed to the subroutine in newpick(12) is simply the contents of attribute 17 of the current item, which may be multi/sub valued.

On exit of the subroutine, newpick(12) contains the value used by jQL.

Example

Comments (in DICT of SALES file)

001 A
002 3
003 Comments
004
005
006
007 B;comments
008
009 T
010 25

Comments (jBC subroutine in available library)

001 SUBROUTINE comments
002 INCLUDE qbasiccommonpick
003 * Interpret Comment code
004 IF newpick(12) = "A" THEN newpick(12) = "Grade 1"
005 IF newpick(12) = "B" THEN newpick(12) = "Grade 2"
006 RETURN

List Sales Comments

SALES........ Comments.................
ABC           Grade 1
DEF           Grade 2

Persistent Variables

When calling subroutines from dictionary items, it is sometimes advantageous for the values of variables to persist between CALLs, for the duration of the jQL execution. An example of how persistent variables can be employed is when it is necessary to READ from a file in the subroutine. Rather than open the file every time the subroutine is called (for each record processed by jQL), it is more efficient to open the file when the first record is processed and keep the file open variable available for subsequent records. This can be achieved with the following code in the subroutine:

...
IF UNASSIGNED(CustFileVar) THEN
    OPEN "CUSTOMER" TO CustFileVar ELSE
        GOSUB FatalError
        ABORT
    END
END
...

In order that the variables are persistent, a compiler directive must be supplied:

• The (V) option to BASIC

• The -JCV option to jbc

• The -V option to jbccom

Persistent variables should be treated as COMMON variables. The one exception is that they are initialized for each jQL command. If a subroutine is called from two or more dictionary items in the same jQL command, then the variables will be shared in the same way the COMMON variables are shared. If the subroutine is called recursively, then the variables will be shared between each level of recursion, in the same way that COMMON variables are shared.

Use the following MC codes to transform text from upper to lower case and vice versa:

Examples

MCL

Assume a source value of AbCdEf. In this case, MCL will return abcdef.

MCT

Assume a source value of AbC dEf “ghi. In this case, MCT will return Abc Def “ghi.

MCU

Assume a source value of AbCdEf. In this case, MCU will return ABCDEF.

The CNV connective allows the query to override the default conversion as supplied in the dictionary with another conversion.

Example

LIST CUSTOMER *A1
CUST.....         *A1......
1                 FRED BLOGGS
2                 TOM JONES
LIST CUSTOMER *A1 CNV “MCT”
CUST.....         *A1......
1                 Fred Bloggs
2                 Tom Jones

Command options are letters enclosed in parentheses, which modify the action of the jQL command sentence. The options described here are common to most commands.

Do not confuse options for commands with options for modifiers and connectives such as HEADING and BREAK-ON. Commas or spaces can separate options; when the options are at the end of the sentence (as is recommended) omit the closing parenthesis. jQL ignores any option, not used by a particular command.

Example

LIST CUSTOMER (HIP

List the SALES file (using the default data definition records) but suppresses the output of a header and the record keys. This command sends the output to the assigned printer.

You can use a colon (:) to concatenate the results of two expressions. For example, the following expression concatenates the character “Z” with the result of sum of fields 2 and 3:

A;”Z”:2 + 3

The MC codes that convert ASCII character codes to their binary or hexadecimal representations or vice versa are:

The MCAB and MCABS codes convert each ASCII character to its binary equivalent as an eight-digit number. If there is more than one character, then MCAB puts a blank space between each pair of eight-digit numbers. MCABS suppresses the spaces.

When converting from binary to ASCII characters, MCBA uses blank spaces as dividers, if they are present. MCBA scans from the right-hand end of the data searching for Elements of “eight-bit” binary strings. If it encounters a space and the element is not eight binary digits long, it prepends zeros to the front of the number until it contains eight digits and continues until reaching the leftmost digit prepending zeros if necessary, it then converts each eight-digit element to its ASCII character equivalent.

Input conversion does not invert. The original code will be applied to input data.

Examples

MCAX

Assume a source value of ABC. In this case, MCAX will return 414243.

MCXA

Assume a source value of 414243. In this case, MCXA will return ABC.

MCAB

Assume a source value of AB. In this case, MCAB will return 01000001 01000010.

MCABS

Assume a source value of AB. In this case, MCABS will return 0100000101000010.

MCBA

Assume a source value of 01000001 1000010. In this case, MCBA will return AB. Note the missing binary digit at the start of the second element of the source value.

MCBA

Assume a source value of 0100000101000010. In this case, MCBA will return AB.

The MC codes that convert numeric values (as opposed to characters) to equivalent values in other number schemes are:

These codes convert numeric values rather than individual characters. For example, conversion of the decimal value of 60 is to X”3C” in hexadecimal, or LX in Roman numerals. The value 60 is converted, not the characters “6” and “0”.

With the exception of MCBX{S} that handles spaces, all conversion of these codes will stop if they encounter an invalid character that is not a digit of the source number system.

With the exception of MCDR, if the conversion fails to find any valid digits, a zero MCDR will return null.

If you submit an odd number of hexadecimal digits to the MCXB code, then it will add a leading zero (to arrive at an even number of characters) before converting the value.

The MCXB and MCXBS codes convert each pair of hexadecimal digits to its binary equivalent as an eight-digit number. If there is more than one pair of hexadecimal digit, MCXB puts a blank space between each pair of eight-digit numbers. MCXBS suppresses the spaces.

When converting from binary to hexadecimal digits, MCBX uses blank spaces as dividers if they are present. MCBX effectively scans from the right-hand end of the data searching for Elements of eight-bit binary digits. If it encounters a space and the element is not a multiple of eight binary digits, it prepends zeros to the front of the number until it contains eight digits. This continues until it reaches the leftmost digit prepending zeros if necessary. Each eight-digit element is converted to a hexadecimal character pair.

Input conversion is effective for MCDR, MCDX, MCRD and MCXD. Input conversion is not inverted for the other codes. The original code will be applied to input data.

Examples

MCBX

Assume a source value of 01000001 1000010. In this case, MCBX will return 4142. It would return the same value if there was no space between the binary source Elements.

MCRD

Assume a source value of MLXVI. In this case, MCRD will return 1066.

MCDX

Assume a source value of 1066. In this case, MCDX will return 42A.

This command reports the total number of records found in a file, which matches the specified selection criteria. The command syntax is:

COUNT file-specifier {record-list} {selection-criteria} {USING file- specifier} {(options}

Syntax Elements

Options can be one or more of the following:

Example

COUNT ORDER WITH ORD.AMT > “1000”
 91 Records counted

Counts the number of records in the SALES file which have a value greater than 1000.

COUNT ORDER WITH ORD.AMT > “1000” (C50
91 Records selected 240 Records processed
91 Records counted

Count the number of records in the ORDER file, which have an ORD.AMT greater than 1000, and display a running total of selected and processed records after each group of 50 records are processed.

Converts dates between internal and external format. The command syntax is:

D{p}{n}{s}

Syntax Elements

• P – The special processing operator and can be any one of the following:

  • D – Returns only the day of the month as a numeric value.

  • I – Returns only dates stored in the external format in internal format. You can use this in field 7 or 8.

  • J – Returns the Julian day (1 - 365, or 1 - 366 for a leap year).

  • M – Returns the number of the month (1 - 12).

  • MA – Returns the name of the month in uppercase letters.

  • Q – Returns the number of the quarter (1 - 4).

  • W – Returns the day of the week as a numeric value (Monday is 1).

  • WA – Returns the day of the week in uppercase letters (MONDAY - SUNDAY).

  • Y – Returns the year (up to four digits).

• n – It is a number from 0 to 4 that specifies the how many digits to use for the year field. If omitted, the year will have four digits and it suppresses the year if n is 0.

• s – It is used as a non-numeric character as a separator between month, date, and year. It must not be one of the special processing operators.

Dates are stored internally as integers, which represent the number of days (plus or minus) from the base date of December 31, 1967.

Example

If you do not specify a special processing operator or an output separator, then the default output format is two-digit day, a space, a three-character month, a space, and a four-digit year. If you specify just an output separator, the date format defaults either to the US numeric format “mm/dd/yyyy” or to the international numeric format “dd/mm/yyyy” (where / is the separator). You can change the numeric format for the duration of a logon session with the DATE-FORMAT command.

Associates controlling and dependent fields. The command syntax is:

D1;fmcd{;fmcd}...
  D2;fmcc

Where,

• fmcd is the field number (FMC) of an associated dependent field.

• fmcc is the field number (FMC) of the associated controlling field.

You can logically group multivalued fields in a record by using a controlling multivalued field and associating other fields with it. For example, you could group the component parts of an assembly on an invoice.

The D1 code in field 8 defines the controlling field and nominates the associated dependent fields. Each dependent field will have a D2 code in field 8.

Outputs the values in the dependent associative fields in order as specified in field 8 of the controlling field. The specified order in the dependent fields in the output specification clause is irrelevant.

Example

LIST CUSTOMER “ABC” CUS.ID .CUS.ORDER

The records in data file CUSTOMER have three associated, multivalued fields, named CUS.ID and CUS.ORDER, and numbered seven, two and five respectively.

CUS.ID is the controlling field because, for each multivalue in this field there will a corresponding value in the other fields, and because CUS.ID should appear first on the report. The data definition record for CUS.ID will have D1;2;5 in field 8.

The data definition records for QTY and PRICE will both have D2;7 in field eight.

The report generated by the command will look something like this:

CUSTOMER CUS.ID CUS.ORDER
 ABC123 AAA 1 10.00
 BBB 11 4.00
 CCC 2 3.30

When executing programs in international mode, it processes all variable contents as UTF-8 encoded sequences. As such all data must be held as UTF-8 encoded byte sequences. This means that data imported into an account configured to operate in international mode must be converted from the data in the current code page to UTF-8. Normally, if all the data are eight bit bytes in the range 0x00-0x7f (ASCII), then no conversion is necessary as these values are effectively already UTF-8 encoded. However, values outside of the 0x00-0x7f range must be converted into UTF-8 proper such that there can be no ambiguity between character set code page values.

For instance, the character represented by the hex value 0xE0 in the Latin2 code page, (ISO-8859-2), is described as “LATIN SMALL LETTER R WITH ACUTE”. However, the same hex value in the Latin1 code page, (ISO-8859-1), is used to represent the character “LATIN SMALL LETTER A WITH GRAVE”.

To avoid this clash of code pages the Unicode specification provides unique hex value representations for both of these characters within the specifications 32-bit value sequence.

Example

Unicode value 0x00E0 is used to represent LATIN SMALL LETTER A WITH GRAVE

Unicode value 0x0155 is used to represent LATIN SMALL LETTER R WITH ACUTE

Another good reason for complete conversion from the original code page to UTF-8 is that doing so also removes the requirement for conversions when reading or writing to files, as this would add massive and unnecessary overhead to all application processing, whereas the conversion from original code page to UTF-8 is a one off cost.

Data definition records (sometimes known as field definition records) define the characteristics of each field in a data file. They specify the output format and the type of processing required to generate each column of a jQL report.

You can use data definition records to:

• Specify default output.

• Associate field names with field numbers (column headings).

• Perform output formatting.

• Calculate new values based on the source data.

• Perform processing via conversion codes.

Although they are normally used to define a single physical field in a file, the data definition records can also be used for operations that are more complex. For example:

• To “join” or derive data from other fields or files

• To verify the presence (or absence) of records in other files

• To format their output in the most easily understood manner (to convert numeric 0 and 1 flags to “Yes” or “No”, for Example, or to output text like “Overdue” if one date field is older than another).

• To generate statistical data like record sizes or counters

The data definition records are usually located in the dictionary of the data file (but not always - see the USING Clause and the Default Output Specification topics). You can set up any number of data definition records. Often, there are several definitions for each field, each one used by a different set of reports which have different output requirements.

You associate the data definition record with a particular field in the data file by specifying the target fields FMC (field-mark count) in field 2 of the data definition record. The FMC refers to (points to) the field number (also known as the line number) of the data within the records of the data file.

When you run a jQL command without specifying specific references to data definition records or without suppressing the output of the report detail, then the system will attempt to locate any default data definition records, which may be set up.

For example, if you issue the command “LIST SALES”, the system will look in the dictionary of the SALES file for a data definition record named “1”. If it finds “1”, this will become the default output for column two. The system will then look for a data definition record named “2” and so until the next data definition record is not found. If “1” is not found in the file dictionary, the system will search the default dictionaries for the same sequence of data definition records.

When you issue a jQL command, which does not contain specific references to data definition records, the system will first attempt to locate each data definition record in the dictionary of the file (or in the file specified in a USING clause). If no data definition is found in the dictionary (or the file specified in a USING clause), the system will look for the data definition in the file defined by the JEDIFILENAME_MD environment variable.

For example, if you issue the command “LIST SALES VALUE”, the system will look in the dictionary of the SALES file for a data definition record named “VALUE”. If it cannot find “VALUE” in the file dictionary, the system will look in the file specified by the JEDIFILENAME_MD environment variable. In this way, you can set up data-specific, file-specific or account-specific defaults for use with any jQL Command.

Record Layout

All data definition records are defined in the same way:

Field|Description

1. D/CODE| – Defines the record as a data definition record. Must be one of the following codes:

A	Marks a normal data definition record.
S	Obsolete but still supported. Was like the A type, but suppressed default column headings when field 3 was blank. Replaced by the A type with a backslash in field 3 to defeat heading.
X	Forces the definition to be ignored if selected as part of a default set of data definitions. Use only when explicitly named. See Default Output Specification later.

2. FMC (field-mark count)| – A field number or special FMC (see Special Field-mark Counts for more details). A field number refers to the corresponding field (or line) in a record. The special FMC codes are:

0	Refers to the record key - field number 0 (zero).
9998	Ordinal number of record at output (used for numbering or counting).
9999	Size of the record in bytes (excluding the key).

3. Column heading| – It has the following features:

   • Heading text, null, or a backslash followed by text.

   • Entering more characters here than the width in field 10 allows, increases the width to accommodate the heading text (this field wins).

   • If the statement uses the COL-HDR-SUPP output modifier or the “C” option, it displays no column headings.

   • Uses heading text as the column heading. If the text is less than the column width, it will be padded with dots.

   • Use spaces to produce a blank heading.

   • Use value marks, (ctrl ]), as NEWLINE characters to place the following text on a new line. If this field is left null, it uses the key of the data definition record as the           column heading.

   • Text following a backslash “\” character will be used as the column heading. If        nothing follows the backslash, the column heading will be null.

4 - 6|Not used.

4. Input/Output conversion codes|– Used for processing the data after sort and selection but before output. See Conversion Codes. Multiple conversion codes, separated by a value marks, will be processed from left to right.

5. Pre-process conversion codes|– Used for processing the data before sort and selection and before field 7 codes. See Conversion Codes later. Multiple conversion codes, separated by a value marks, will be processed from left to right.

6. Format| – Specifies the layout of the data within the column. Can be any of the following:

L Left justified	If the data exceeds the column width specified in field 10, the data is broken at column width without respect to blank spaces.
R Right justify	If the data exceeds the column width specified in field 10, it truncates the data.
T Text. Word wrap - like L	(Left justified) Where possible, lines will be broken at the blank space between words.
U Unlimited.	Data is output on one line ignoring column boundaries.

7. Width| – Numeric value specifying the column width. If the number of characters in field 3 (the heading) is greater than the number entered here, the number of characters in field 3 will be used.

Special Field-mark Counts

Three special FMCs (field-mark counts) are recognized: 0, 9998 and 9999.

FMC 0 - RECORD KEY

Setting field 2 of the data definition record to 0 (zero) causes the system to work with the record key. In this way, you could set up a data definition record, which would allow the record keys to be output in a column other than the first, and to use any column heading.

Typically, you would also use the ID-SUPP modifier or the “I” command option to suppress output of the record key in the first column.

FMC 9998 - RECORD COUNT/NI OPERAND

Setting field 2 of the data definition record to 9998 causes the system to return a record (or line) count equal to the number of records output so far in the report.

You could also use function operators within an A or F conversion code in field 7 or 8 of the data definition record to achieve the same result. Function code operand NI yields the same value as an FMC of 9998.

FMC 9999 - RECORD SIZE/NL OPERAND

Setting field 2 of the data definition record to 9999 causes the system to return the record size in bytes. The size does not include the key but does include all field marks within the record.

You could also use function operators within an A or F conversion code in field 7 or 8 of the data definition record to achieve the same result. Function code operand NL yields the same value as an FMC of 9999.

Default Output Specification

Default output specifications work in two ways, depending on whether the default definitions are explicit or implicit.

• Explicit Defaults – If you specify and use a data definition record for output, the system will search the implied dictionary first (or the dictionary specified in a USING clause). If the data definition is not found in the implied dictionary, the system will look for the data definition in the file defined by the JEDIFILENAME_MD environment variable.

• Implicit Defaults – If you do not explicitly specify or use any data definition records for output, the system will search for a predefined series of records based on the search criteria outlined in the preceding section.

You can therefore set up a series of data definition records, which the system will use if a jQL Command sentence does not include any explicit output file, Ids.

You must name these “default” records in a numeric sequence starting at 1 (1, 2, 3, and so on). The fields, which these records define, will be output in the same sequence as the keys but they do not need to follow the same sequence as the fields in the file.

When a jQL Command sentence with no explicit output fields is issued, the system first looks in the dictionary for a data definition record named 1, then for a record named 2, then 3, and so on until it fails to find a record with the next number. Will use the record if it has a D/CODE of A; it ignores the record if it has a D/CODE of X, but it will not break the sequence.

Will skip a record with a D/CODE of X if it was found as the result of a search for defaults; Under normal circumstances it can be used in the same way as any other data definition record.

This means that when you first set up a series of “default” data definition records, you should put an A in the D/CODE field of each. If you subsequently need to remove one from the sequence, you can simply change the D/CODE field to an X. This way you do not break the sequence or have to copy the remaining “default” records to new names in order to fill the gap.

You can still use a data definition record with a number for a key in the same way as any other data definition record.

Predefined Data Definition Records

Some predefined data definition records are automatically available so that, if appropriate data definition records are not included in a files dictionary, you can still generate a report. These records are recognized when used in a jQL Command sentence.

The predefined data definition records are named *A0 to *Annn. The numeric portion of the key corresponds to the position of the field they report on and the column heading will be the same as the DDR name.

This command deletes selected records from a file according to record list or selection criteria clauses. The command syntax is:

EDELETE file-specifier [record-list | selection-criteria]

EDELETE requires an implicit or explicit record list, or selection criteria. Preceding the command with a SELECT, GET-LIST or other list-providing command can provide an implicit list. EDELETE will immediately delete the specified records. To clear all the records in a file, use the CLEAR-FILE Command.

Examples

EDELETE ORDER “ABC” “DEF”
2 Records deleted

Deletes the records ABC and DEF based on the explicit list of records.

EDELETE ORDER IF ORD.AMT < “500”
n Records deleted

Delete all records in the ORDER file in which the ORD.AMT field IS LESS THAN 500.

SELECT ORDER WITH ORD.AMT = “500”
n Records selected
EDELETE ORDER
 n Records deleted

Selects all records in the ORDER file in which the ORD.AMT field = 500, and deletes them.

This command generates an implicit list of records in a file if they contain (or do not contain) one or more occurrences of specified character strings. The command syntax is:

ESEARCH file-specifier {record-list} {selection-criteria} {sort-criteria} {USING file-specifier} {(options}

Syntax Elements

Prompt: At the prompt, supply one or more search strings:

String: Enter the required character string and press <ENTER>. This prompt is repeated until only <ENTER> is pressed. You can enter unlimited characters.

Do not enter double quotes unless they are part of the string to search.

When the Command terminates (unless the “S” option is used), it displays the total number of entries in the generated list. The list is then available as if generated by a SELECT, GET-LIST or other list-providing Command. If you do not specify a sort criteria clause, the record list will be unsorted.

Example

ESEARCH ORDER (I
STRING: ABC
STRING: DEF
KEY1
 KEY2
18 Records selected
>

Generates a list of all records in the ORDER file, which contain the strings ABC or DEF.

EXPLAIN displays information about how the statement will be processed. This lets you decide if you want to rewrite the query more efficiently.

EXPLAIN lists the files, indexes, sorts, etc. included in the command, along with access timings in a report format. The command syntax is:

EXPLAIN <<Command>>

For example,

EXPLAIN LIST JCUSTOMERS FIRSTNAME LASTNAME BY LASTNAME BY LASTNAME EXPLAIN SSELECT JCUSTOMERS SAVING FIRSTNAME LASTNAME

EXPLAIN Reports

Currently there is only one type of report generated for jQL queries. The aim of the report is to provide a simple representation of what has just happened, along with some information of how long things have taken and what objects have been used. This allows you to alter your commands to make them more efficient.

jQL Report

A text report is generated that breaks the command into its individual components. Internally each operation is grouped and given a description to try and make what the statement does a little easier to understand. It should give you a basic understanding why something is taken so long and where an index may help. 

The output is broken down into the following groups:

Command	The command you just ran.
File	Details of the file used in your query (file type and access methods used).
Indexing	Details on what indexes have been used and any limiting statements that would affect them.
Sorting	Details of what columns are used for sorting and information on what attributes associated with the column will affect the output.
Output Columns	List of columns returned from query.
Aggregate	List of things that the break processor will process.
Timings	List of how long each access type has taken. The output looks like below. Sort Processor Total Taken: 16 (1...1) Total Taken is split into total time for all records, followed by minimum and maximum access times in milliseconds. A good example for access timings is that you can use multiple methods to access data, Simple dictionary 001: D 002: 1 … Simple dictionary but indexed ITYPE Dictionary 001: I 002: THEFIRSTDICTIONARY;@1 … EVAL Statement EVAL “A+B” Subroutine call 001: I 002: SUBR(XXXX) All of the above would take a different amount of time to return a result; the timings are not necessarily how long it took to read the file, but how long it took to get a single row using the current statement. So a combination of index access timings, full file source, etc. are used to give an overview of what all of the components used in the current statement.

An explicit item-id lists items for processing, which encloses each item-id in double quotes. Spaces between item-ids are optional. An item-id list cannot include a relational operator and ignores any included logical connectives.

JQL treats the values you place between quotes as item-ids, not as string values. This treats the left ignore, right ignore and wild card as ordinary characters and not as special characters.

The command syntax is:

‘item-id’ {‘item-id’}.. .

Item Lists

An item list specifies the items within the file to be further processed. If no list is given, all items in the file are implied. For example,

The following sentence lists ORDER information with numbers that are both greater than or equal to 200 and less than 700:

LIST ORDER >= ‘200’ AND LT ‘700’

The following sentence displays information about orders with numbers less than 200 and with available dates after May 17 2002.

LIST ORDER < ‘200’ WITH AVAILABLE AFTER “MAY 17 2002”

The following sentence displays CUSTOMER information 500 and greater than 199 and with CUSTOMER ADDRESS. The second AND arises because the sentence includes both item selection and data selection criteria: these operations perform one after the other, giving an effective AND function. The OR between “ST” and “D” is implicit.

LIST CUSTOMER LT ‘500’ AND GT ‘119’ WITH CUS.ADDR “ST” “D”

The following sentence lists rooms with numbers less than 200 or greater than 399.

LIST orders < ‘200’ OR > ‘399’

Use the following MC codes to extract characters from a string:

Examples

MCA

Assume a source value of ABC*123!DEF. In this case, MCA will return ABCDEF.

MC/A

Assume a source value of ABC*123!DEF. In this case, MC/A will return *123!.

MCB

Assume a source value of ABC*123!DEF. In this case, MCB will return ABC123DEF.

MC/B

Assume a source value of ABC*123!DEF. In this case, MC/B will return *!.

MCN

Assume a source value of ABC*123!DEF. In this case, MCN will return 123.

MC/N

Assume a source value of ABC*123!DEF. In this case, MC/N will return ABC*!DEF.

F codes provide many facilities for arithmetic, relational, logical, and concatenation operations. The expression of all operations is in Reverse Polish notation and involves the use of a “stack” to manipulate the data.

Syntax Summary

There are three forms of the F code:

• F – Uses only the integer parts of stored numbers unless a scaling factor is included. If the      JBCEMULATE environment variable is set to “ROS”, the operands for “-”, “/” and concatenate are used in the reverse order.

• FS – Uses only the integer parts of stored numbers (use SMA standard stack operations for all emulations).

• FE – Uses both the integer and fraction parts of stored numbers.

Command Syntax

F{n};elem{;elem}...
  FS;elem{;elem}...
  FE;elem{;elem}

Syntax Elements

• n – A number from 1 to 9 used to convert a stored value to a scaled integer. The stored value explicit or implied decimal point is moved n digits to the right with zeros added if necessary. Returns only the integer portion of this operation.

• elem – Any valid operator.

F codes use the Reverse Polish notation system. Reverse Polish is a postfix notation system where the operator follows the operands. The expression for adding two elements is “a b + “ (the usual algebraic system is an infix notation where the operator is placed between the operands, for example, “a + b”).

The F code has operators to push operands on the stack. Other operators perform arithmetic, relational, and logical operations on stack elements. There are also concatenation and string operators.

Operands pushed on the stack may be constants, field values, system parameters (such as date and time), or counters (such as record counters).

By default, formatting and conversion are defined by a field’s DICTionary entry. If the behaviour defined in the DICTionary needs to be overridden for any reason, then there are a number of different qualifiers that may be used in jQL statements

The syntax is:

Example

>LIST HAT.TYPE HAT.SIZE DISPLAY.LIKE PRICE COL.HDG “Hat sizes available”
HAT.TYPE          Hat sizes available
Trilby                        10
Top Hat                       13

File modifiers like DICT, ONLY=, WITHIN and TAPE modifies the use of the file, and how it is accessed.

The command syntax is:

{DICT} {ONLY} {WITHIN} {TAPE} filename{,data-section-name}

Syntax Elements

The FMT connective allows the query to override the formatting, which is used to display the corresponding data with a different format mask. For example,

LIST CUSTOMER *A1
CUST.....         *A1...
1                 FRED B
2                 TOM JO
LIST CUSTOMER *A1 FMT “25L”
CUST.....         Customer name
1                 FRED BLOGGS
2                 TOM JONES

You can format the result of any "A" code operation by following the expression with a value mark, and then the required format code:

An;expression]format

Format codes can also be included within the expression. There are three ways to use format codes:

• Transform the result of the F code

• Transform the content of a field before it is pushed on the stack

• Transform the top entry on the stack

The command syntax is:

f-code{]format-code...}
  field-number(format-code{]format-code}...)
  (format-code{]format-code}...)

Where,

• f code – A complete F Code expression.

• field number – The field number in the record from which to retrieve the data.

• format code – Any valid format codes.

• ] – Represents a must use value mark (ctrl ]) to separate each format code.

Example

F;2(MD2]G0.1);100;-

The above command:

1. Obtains the value of field 2.

2. Applies an MD2 format code.

3. Applies a group extract to acquire the integer portion of the formatted value, and push the result onto the stack.

4. Subtracts 100 from the field 2 formatted, group-extracted value and return this value.

   Note that under ROS emulation, the value returned would be the result of subtracting the integer value from the group extract, from 100, which is:

   100 - OCONV(OCONV(Field2, “MD2”), “G0.1” )

G codes extract one or more contiguous strings (separated by a specified character), from a field value. The command syntax is:

G{m}xn

Where,

• m – The number of strings to skip. If omitted or zero, then extraction begins with the first character.

• x – The separation character.

• n – The number of strings to be extracted.

The field value can consist of any number of strings, each separated by the specified character. The separator can be any non-numeric character, except a system delimiter.

If m is zero or null and the separator x is not found, then the whole field will be returned. If m is not zero or null and the separator x is not found, then null will be returned.

Input conversion does not invert. It simply applies the group extraction to the input data.

Example

G0.1

If the field contains “123.45”, then 123 will be returned. You can also use “G.1” to achieve the same effect.

G2/1

If the field contains “ABC/DEF/GHI”, then GHI will be returned.

G0,3

If the field contains “ABC,DEF,GHI,JKL”, then ABC,DEF,GHI will be returned. Note that the field separators are included in the returned string.

It specifies the text to replace the default asterisks in the cumulative total line at the end of the report. CAPTION is a synonym for GRAND-TOTAL.

Enter a heading or footing option, which specify a value in the order in which they appear. Text spaces are not normally required between option codes. However, you can present options that represent values such as pages or dates without spaces. For example, ’”PD”’ will print on the first page as:

111/11/00

In this case, enter the options with a space between them like this “‘P’ ‘D’”

Example

SORT ORDER BY ORD.ID. BREAK-ON ORD.ID ‘BL’ ORD.QTY GRAND-TOTAL “Total “HEADING ORD.QTY : ‘B’ ‘DL’” FOOTING “PAGE ‘CPP’ “LPTR

The above command:

• Control Breaks on a change in ORD.ID and suppress the LINE FEED before the break. Reserve the break value for use in the heading (‘B’). Maintain a running total of the VALUE field and output it at each control break.. Put the word Total on the GRAND-TOTAL line.

• Sets up a heading for each page, which comprises the words ‘ORD.QTY:’, the ORDER code (from the break), a date and a line feed. Set up a footing, this contains the text ‘PAGE’, and a page number, centered on the line.

• Produces the report on the currently assigned printer.

This command displays the entire contents of items in a file, including the system delimiters. The command syntax is:

I-DUMP file-specifier {record-list} {selection-criteria} {sort-criteria} {USING file-specifier} {(options}

Use the S-DUMP command to produce sorted output.

System delimiters are denoted as:

Example 1

I-DUMP CUSTOMER WITH CUS.CITY = “BEAVERTON”

This generates the following output:

40840^Lexus of Portland^8840 Sw Canyon Rd.^Beaverton^OR^^503-297-9017^^^
40848^Kuni Cadillac & BMW^3725 SW Cedar Hills Blvd^Beaverton^OR^^503-643-1543^^
^
40855^Berg Car Company^10680 SW Canyon Rd.^Beaverton^OR^^503-641-1251^^^
40821^Beaverton Chrysler Plymouth^10760 SW Canyon Rd.^Beaverton^OR^^503-646-051
6^^^

EXAMPLE 2

jsh machinename ~ -->S-DUMP CUSTOMER BY CUS.ADDR WITH CUS.NAME "A..."

This generates the following output:

40854^AA Auto Brokers^^^^^503-774-6701^^^
40813^Acura Of Portland^12560 SE Start^Portland^OR^98330^503 544 3032^503 544 3
958^^
40811^All Car Rental^1321 East 78th St, Suite 101^Bloomington^MN^55425^612 854
4045^612 851 9361^mglomb@acrental.com^
40873^Andy's Auto Supply & Repair^2150 SE Powell Blvd.^Portland^OR^^503-232-086
8^^^30058493^
40819^Alexander Chrysler-Plymouth^2340 NE Sandy Blvd.^Portland^OR^^503-233-4433
^^^

The jBASE jQL processor supports I-TYPES as imported from PRIME or Universe. jQL has been enhanced to support D and I type attribute definition records.

Formats

I-TYPE	D-TYPE
001 I	001 D
002 Expression	002 AttributeNo
003 Conversion	003 Conversion
004 Header	004 Header
005 Format	005 Format
006 - 016 Reserved	006 - 016 Reserved

Expression

This can be one or more of the following types:

Type	Description
Dictionary Id	Attribute definition S/A/D/I/X/V type (Note: V is equivalent to I)
@Variables	@RECORD, @ID, @USERNO, @DATE, @TIME
Functions	TRANS (File, Item, Attr, Code)
User Subroutines	MYSUB (param1, param2, param256)
Conditionals	IF X = Y THEN @RECORD ELSE " "
String Extraction	Expression[6,4]

You can define multiple expressions within the same I-TYPE.

Example

Expression ; Expression ;

Expressions can be parenthesized, contain numeric constants, string literals, enclosed in single or double quotes, and extended operators such as EQ, NE, LE, GT, CAT, AND, OR, MATCHES.

If you use an I-TYPE for the first time in a query (jQL Command), then the expression attribute will be “compiled” to produce internal op codes and parameter definitions. This mechanism provides greater efficiency at run time. However, to ensure it compiles all I-TYPE definitions, rather than on an adhoc basis, a utility ICOMP has been provided. The command syntax is:

ICOMP {DICT} FileName {RecordList | * }

Where,

• FileName    – The name of the file to convert.

• RecordList    – The list of the Record identifiers.

IF Statement gives the A code conditional capabilities. The command syntax is:

IF expression THEN statement ELSE statement

Where,

• expression must evaluate to true or false. If true, executes the THEN statement. If false, executes the ELSE statement.

• statement is a string or numeric value.

Each IF statement must have a THEN clause and a corresponding ELSE clause. You can nest statements but the result of the statement must evaluate to a single value. The words IF, THEN and ELSE must be followed by at least one space.

Examples

A;IF N(QTY) < 100 THEN N(QTY) ELSE ERROR!

Tests the QTY value to see if it is less than 100. If it is, output the QTY field. Otherwise, output the text “ERROR!”.

A;IF N(QTY) < 100 AND N(COST) < 1000 THEN N(QTY) ELSE ERROR!

Same as the example above except that QTY will only be output if it is less than 100 and the cost value is less than 1000.

A;IF 1 THEN IF 2 THEN 3 ELSE 4 ELSE 5

If field 1 is zero or null, follow else and use field 5.

Else test field 2, if field 2 is zero or null, follow else and use field 4.

Else, use field 3. Use Field 3 only if both fields 1 and 2 contain a value.

To provide an implicit item-id list, execute a verb such as SELECT or GET-LIST immediately before executing a jQL command. If you also specify item-id selection, the jQL processor effectively ANDs its result with the implicit item-id list to limit further the items selected.

If you specify an explicit item-id list, the processor ignores any implicit list.

Examples

The following sentences will not list anything because the string values cannot match any item-id in the implicit list.

SELECT ORDER GT ‘200’
 42 RECORDS LISTED
 >LIST CUSTOMER = ‘40823’ ‘40825’

The following sentences list information about CUSTOMER 40823 and 40825 because the process ignores an implicit item-id list when an implicit item-id list is in the sentence.

SELECT ORDER GT ‘200’
 23 items selected
 > SELECT CUSTOMER ‘40823’ ‘40825’

Item-id Selection clause

An item-id selection clause expresses limits on the value of item-ids, for selection for processing. It has at least one value string that defines an item-id or part of an item-id, and an explicit relational operator must precede at least one value string. The relational operator is what makes jQL treat item-id selection differently from an explicit item list. You can use logical connectives to combine relational operations. If you do not use an explicit logical connective, jQL defaults to the OR connective. JQL searches the file for each item-id that matches the value strings in the criteria. If an implicit item-id list has been specified, the processor checks only those item-ids present in the list.

Selection Criteria Clause

The selection criteria clause allows you to specify data-specific limits on the range of records that will be eligible for processing.

If a record list of any type is outstanding when processing reaches the selection criteria, only those in the list will be submitted to the selection process. If there are no record lists outstanding, then the selection process considers all records in the file.

Each selection criterion specifies a field (data or key) for testing to determine selection of a record. The selection criterion begins with the connective (WITH (or IF) and must include a field name. The field name can be followed by a value selection clause otherwise, it defaults to NE ““(not equal NULL).

The command syntax is:

WITH | IF { NOT } { EACH } field {value-selection clause} {{AND | OR}
{ WITH |IF } {NOT} {EACH} field {value-election clause}...}

Value selection clause has the form:

{relational-operator} “value string” {{logical-connective} 
{relational operator} “value string”}. . .

Where,

WITH or IF is the selection connective. It must be the first word of a selection criterion. WITH and IF are synonymous. WITHOUT is a synonym for WITH NOT.

The following statements enable jBASIC programmers to deal directly with jQL statements, thereby eliminating the need to parse the output of commands such as EXECUTE.

jBASE jQL enables you to call Basic subroutines from within correlatives and conversions. There are two flavors of subroutine and each requires a different include file. For Advanced Pick subroutines, the developer must include the following header file from the “include” subdirectory in the jBASE release directory.

qbasiccommonpick

For Sequoia subroutines, the developer must include the following header file from the “include” subdirectory in the jBASE release directory.

qbasiccommonseq

For dates and times, simple date format functions have been applied to use the configured locale to support the standard conversions D and MTS. Formatting numbers via MR/ML/MD, use locale for Thousands, Decimal Point and Currency notation.

This section details the various jQL operands.

Field Number (FMC) Operand

The field number operand returns the content of a specified field in the data record:

field-number{R{R}}

The first R specifies that any non-existent multivalues should use the previous non-null multivalue. When the second R is specified, any non-existent subvalue uses the previous non-null subvalue.

The following field numbers have special meanings:

0	Record key
9998	Sequential record count
9999	Record size in bytes

Examples

A;9999

Returns the size of the record in bytes.

A;2 + 3R

For each multivalue in field 2, the system also obtains the (first) value in field 3 and adds it. If field 2 contains 1]7 and field 3 contains 5, then the result would be two values of 6 and 12 respectively. Where field 3 does not have a corresponding multivalue and will use the last non-null multivalue.

A;2 + 3RR

For each subvalue in field 2, the system also obtains the corresponding subvalue in field 3 and adds it. If field 2 contains 1\2\3]7 and field 3 contains 5\4 the result would be four values of 6, 6, 7, 12 and 4 respectively.

Field Name Operand

The field name operand returns the content of a specified field in the data record:

N(field-name){R{R}}

Where,

• field-name is the name of another field defined in the same dictionary or found in the list of default dictionaries.

• R specifies that the value obtained from this field is applied for each multivalue not present in a corresponding part of the calculation.

• RR specifies that the value obtained from this field is applied for each subvalue not present in a corresponding part of the calculation.

Any pre-process conversion codes in the specified field-name including any further N(field-name) constructs are processed as part of the conversion code.

N(field-name) you can nest constructs up to 30 levels. The number of levels is restricted to prevent infinite processing loops. For example:

TEST 1
008 A;N(TEST2)

 TEST 2
008 A;N(TEST1)

If the data definition record of the specified field contains field eight pre-process conversion codes, then these are applied before the values are returned.

Examples

A;N(S.CODE)

Returns the value stored in the field defined by S.CODE.

A;N(A.VALUE) + N(B.VALUE)R

For each multivalue in the field defined by A.VALUE, the system also obtains the corresponding value in B.VALUE and adds it. If A.VALUE returns 1]7 and B.VALUE returns 5, the result would be two values of 6 and 12 respectively.

A;N(A.VALUE) + N(B.VALUE)RR

For each subvalue in the field defined by A.VALUE, the system also obtains the corresponding value in B.VALUE and adds it. If A.VALUE returns 1\2\3]7 and B.VALUE returns 5 the result would be four values of 6, 7, 8 and 12 respectively.

Literal Operand

The literal operand supplies a literal text string or numeric value:

“literal”

Where, literal is a text string or a numeric constant. It assumes a number not enclosed in double quotes to be a field number (FMC).

Examples

A;N(S.CODE) + "100"

Adds 100 to each value (subvalue) in the field defined by S.CODE.

A;N(S.CODE):"SUFFIX"

Concatenates the string "SUFFIX" to each value (subvalue) returned by S.CODE.

System Parameter Operands

Several A code operands return the value of system parameters. They are:

Operand	Description
D	Returns the system date in internal format.
LPV	Returns the previous value transformed by a format code.
NA	Returns the number of fields in the record.
NB	Returns the current break level counter. 1 is the lowest break level, 255 is the GRAND TOTAL line.
ND	Returns the number of records (detail lines) since the last control break.
NI	Returns the record counter.
NL	Returns the record length in bytes.
NS	Returns the subvalue counter.
NU	Returns the date of last update.
NV	Returns the value counter.
T	Returns the system time in internal format.
V	Returns the previous value transformed by a format code.

Special Operands

The following operands allow you to use special functions:

I(expression)	Returns the integer part of expression.
R(exp1, exp2)	Returns the remainder of exp1 divided by exp2.
S(expression)	Returns the sum of all values generated by expression.
string[start-char-no, len]	Returns the substring starting at character start-char-no for length len.

Example

AE;I(N(COST) * N(QTY))

Returns the integer portion of the result of the calculation.

By default, it displays output from a jQL command on your terminal in columnar format, with a pause at the end of each page (Full screen).

Output Device

You can redirect the output to a printer (or the currently assigned Spooler device) by using the LPTR format specifier or the P option.

Report Layout

If the columnar report will not fit in the current page width of the output device, it will be output in “non-columnar” format where each field of each record occupies one row on the page.

Paging

If the displayed report extends over more than one screen, press <ENTER> to view the next screen. To exit the report without displaying any remaining screens, press <Control X> or “q”.

JQLCOMPILE compiles a jQL statement. The command syntax is:

JQLCOMPILE(Statement, Command, Options, Messages)

Where,

• Statement is the variable, which will receive the compiled statement (if it compiles); most other functions use this to execute and work on the result set etc.

• Command is the actual jQL query that you want to compile (such as SELECT or something similar). Use RETRIEVE to obtain fetchable data records, as the verb rather than an existing jQL verb. This will ensure that the right options are set internally. In addition, use any word that is not a jQL reserved word as the verb and it will work in the same way as RETRIEVE. Implement a PLOT command that passes the entire command line into JQLCOMPILE and the results will be the same as if the first word were replaced with RETRIEVE.

• Options: You must specify JQLOPT_USE_SELECT to supply a select list to the JQLEXECUTE function; the compile builds a different execution plan if using select lists.

• Messages:  If the statement fails to compile, this dynamic array is in the STOP format, and therefore you can program and print STOP messages, which provides a very useful history of compilation for troubleshooting purposes. It returns -1 if a problem was found in the statement and zero if there was not.

JQLEXECUTE starts executing a compiled jQL statement. The command syntax is:

JQLEXECUTE(Statement, SelectVar)

Where,

• Statement is the valid result of a call to JQLCOMPILE(Statement, …).

• SelectVar is a valid select list that is used to limit the statement to a predefined set of items. For example:

  SELECT PROGRAMMERS WITH IQ_IN_PTS > 250
  1 Item Selected
  LIST PROGRAMMERS NAME
   
  PROGRAMMERS...    NAME
  0123             COOPER, F B
  

  This function returns -1 in the event of a problem, such as the statement variable not being correct. It will cause the statement to run against the database and produce a result set for use with JQLFETCH().

JQLFETCH fetches the next result in a compiled jQL statement. The command syntax is:

JQLFETCH(Statement, ControlVar, DataVar)

Where,

• Statement is the result of a valid call to JQLCOMPILE(), followed by a valid call to JQLEXECUTE().

• ControlVar will receive the ‘control break’ elements of any query. For example, if there are BREAK values in the statement, and you want the totals, they will be described here.

  The format of ControlVar is:

   Attr 1              Level: 0 means detail line 1 - 255            for the control breaks, the same as the A correlative NB.
  Attr2               Item ID  
  Attr 3              Break control
  Value is 1 if a blank line should be output first.
  Attr 4              Pre-break value for 'B' option in header
  Attr 5              Post-break value for 'B' option in header
  

• DataVar will receive the actual data sent to the screen on a LIST statement for instance. The format is one attribute per column.

  Applies Attribute 7 Conversions (or attribute 3 in Prime-style DICTS) to the data.

If you set the property STMT_PROPERTY_FORMAT, then it also formats each attribute according to the width and justification of the attribute definition and any override caused by the use of FMT, of DISPLY.LIKE on the command line.

This command gets the property of a compiled jQL statement. The command syntax is:

JQLGETPROPERTY(PropertyValue, Statement, Column, PropertyName)

Syntax Elements

This command sets a property in a compiled jQL statement. The command syntax is:

JQLPUTPROPERTY(PropertyValue, Statement, Column, PropertyName)

Where,

• PropertyValue is the value you want to set the specified property, such as 1 or “BLAH”

• Statement is the result of a valid JQLCOMPILE() function. Some properties may require JQLEXECUTE() first.

• Column holds 0 for a general property of the statement, or a column number if it is something that can be set for a specific column.

• PropertyName – These are EQUATED values defined by including the file JQLINTERFACE.h. There are lots of these and someone is going to have to document each one.

This function returns -1 if it locates a problem in the statement and 0 for no problem.

L codes return the length of a value, or the value if it is within specified criteria. The command syntax is:

L{{min,}max}

Where,

• min – Specifies that the process is to return an element if its length is greater than or equal                           to the minimum number.

• max – Specifies that the process is to return an element if its length is less than or equal to                            the maximum number.

The L code by itself returns the length of an element when used with max or min and max the L code returns the element if it is within the length specified by min or max.

Examples

L - Assuming a value of ABCDEF, returns the value 6.

L4

If JBCEMULATE is set to ROS, L4 is translated as return the value if its length is less than or equal to 4 - the equivalent of L0,4. Assuming a value of ABCDEF, L4 will return null - the value is longer than 4 characters.

If JBCEMULATE is not set to ROS, L4 is translated as return the value if its length is exactly equal to 4 - the equivalent of L4,4. Assuming a value of ABCDEF, L4 will return null - the value is longer than 4 characters.

L4,7

L4,7 is translated as return the value if its length is greater than or equal to 4 and less than or equal to 7. Assuming a value of ABCDEF, L4,7 will return ABCDEF.

The following sentence lists information about all the CUSTOMER code numbers ending in 00.

LIST CUSTOMER = ‘[00’

The following sentence does not list any rooms because there is no relational operator, the value [23 is treated as an item-id.

LIST CUSTOMER ‘[23’

Outputs data in a format suitable for producing labels. The command syntax is:

LIST-LABEL file-specifier {record-list} {selection-criteria} {sort-criteria} {USING file-specifier}{output-specification} {format-specification} {(options}

Prompts

At the prompt, supply formatting criteria as follows:

COL, ROW, SKIP, INDENT, SIZE, SPACE{C}:

Where,

The total number of columns specified must not exceed the page width, based on the calculation:

COLs * (SIZE + SPACE) + INDENT <= page width

ROW must be a minimum of one for each field, plus one for the record key (if not suppressed). If the record keys are not suppressed, the first row of each label will contain the record key.

If INDENT is not zero, at the prompt supply a series of HEADERs that will appear in the left margin for each field. If a heading is not required for a particular line, press <ENTER>.

Multivalued fields appear as separate labels.

If specified, COL-HDR-SUPP or HDR-SUPP, or the C or H options, the page number, date, and time will not be output and generates the report without page breaks. You must specify a sort criteria clause to sort the records.

Example

LIST-LABEL ORDER ORD.ID ORD.CUS.REF ID-SUPP (C
COL,ROW,SKIP,INDENT,SIZE,SPACE(,C): 2,5,2,0,25,4,C
PAGE    1                                            13:06:26  15 NOV 2004
Order                    30058475  30058488
Orderid                  404343  235
Customer Ref
Order                    30058501  30058476
Orderid                  PO 30232  0500444
Customer Ref
Order                    30058489  30058502
Orderid                  658  2202FR
Customer Ref
Order                    30058490  30058477
Orderid                  1254  PO 76876

Generates a formatted report of records and fields from a specified file. The command syntax is:

LIST file-specifier {record-list} {selection-criteria} {sort-criteria} {USING file-specifier} {output-specification} {format-specification} {(options}

If providing no output specification clause, the system searches for default data definition records (named 1, 2 and so on) in the file dictionary and then in the file specified in the JEDIFILENAME_MD environment variable. If no default data definition records are found, it lists only the record keys. You must specify a sort criteria clause to sort the records.

Examples

LIST ORDER

List all the records in the SALES file and use the default data definition records (if found) to format the output.

LIST ORDER “ABC” “DEF” “GHI”

List the records from the ORDER file with key values of ABC, DEF or GHI. Use the default data definition records (if found) to format the output.

GET-LIST ORDER
>LIST ORDER GT “DEF”  

Get the previously saved list called ORDER.Q4 and, using the list, report on the records in the ORDER file which have a key greater than DEF. Use the default data definition records (if found) to format the output.

LIST ORDER WITH ORD.ID = “ABC]” OR “[DEF”

List the records in the ORDER file in which the ORD.ID field contains values which start with ABC or end with DEF. Use the default data definition records (if found) to format the output.

LIST ORDER WITH NO ORD.ID = “ABC]” OR “[DEF” (P

List the records in the ORDER file in which the ORD.ID field does not contain values which start with ABC or end with DEF. Output the report to the printer. Use the default data definition records (if found) to format the output.

LIST order BY ORD.AMT BREAK-ON ORD.AMT “”BL” ORD.ID ORD.COST GRAND-TOTAL “Total”
HEADING “Sales Code: “B” “DL” FOOTING “Page “CPP” LPTR

Sort the ORDER file by ORD.AMT. Output the ORD.AMT, ORD.ID and ORD.COST fields.

Control break on a change in ORD.AMT and suppress the LINE FEED before the break. Reserve the break value for use in the heading (“B”).

Maintain a running total of the ORD.COST field and output it at each control break.

Put the word “Total” on the grand-total line.

Set up a heading for each page, which comprises the words “Sales Code: “, the sales code (from the break), a date and a LINE FEED. Set up a footing, which contains the text “Page”, and a page number, centered on the line?

Produce the report on the currently assigned printer.

Generates a report of all data definition records in the first MD file found, or the specified file. The command syntax is:

LISTDICT {file-specifier}

Where, file-specifier specifies a dictionary file other than a file named MD in the JEDIFILEPATH.

If you do not specify a file-name, LISTDICT will work with the first file named MD, it finds in your JEDIFILEPATH.

The logical connective AND or OR joins two relational expressions. The default connective is OR. If giving two relational expressions without a logical operator between them, items satisfying either expression are selected (as if the OR connective had been used).

The connective AND yields a truth-value of true if all the truth-values it is combining are true. If any truth-value is false, the result of the AND connective is false. The OR connective yields a truth value of true if at least one of the truth values it is combining is true.

The logical operators test two expressions for true (1) or false (0) and return a value of true or false. Logical operators are:

• AND| – Returns True if both expressions are true.

• OR| – Returns True if any expressions is true.

The words AND and OR must be followed by at least one space. The AND operator takes precedence over the OR unless you specify a different order by means of parentheses. OR is the default operation.

Logical operators include a logical AND test and a logical inclusive-OR test. Logical operators are:

Macros contain predefined or often used elements of a jQL sentence, stored on the system like data definition records and are specified in the command sentence in a similar way.  When submitting a command containing one or more macros for execution it expands and includes the macro references in the sentence. You can substitute macros for any element of the command sentence except the command itself and the filename.

The search for macro definition records is in the same way as data definition records. Do not use a jQL keyword for a Data Definition record.  The first field of a macro definition must contain the letter M. The remaining fields are either command elements or comment lines (indicated by a leading asterisk ‘*’ and a space).

You can nest macros - a macro can refer to another macro - but the resulting command sentence must still follow the same rules as a normal jQL sentence. When nesting macros, beware of infinite loops where for example, macro A calls macro B that calls macro A that calls macro B.

Example

SORT ORDER BY ORD.COST STD.HEADING

In this example, STD.Heading is a macro, which contains a standard heading clause:

STD.HEADING
 001 M
002 * Standard heading for sales reports
003 Heading “SALES - COMPANY PRIVATE’LL’PAGE ‘PL’”
004 LPTR

When the sentence expands it will look like this:

SORT SALES BY S.CODE HEADING “SALES - COMPANY PRIVATE’LL’PAGE ‘” LPTR

MC codes include facilities for:

• Changing characters to upper or lower case

• Extracting a class of characters

• Replacing characters

• Converting ASCII character codes to their hexadecimal or binary representations and vice versa

• Converting a hexadecimal values to their decimal or binary equivalents and vice versa

• Converting a decimal value to its equivalent in Roman numerals and vice versa

One source of confusion when using MC codes is that input conversion does not always invert the code. If most MC codes are used in field 7 of the data definition record, applies the code in its original (un-inverted) form to the input data Therefore, you should always try to place MC codes into field 8 of the data definition record. The exceptions to this, is where input conversion is effective, are clearly indicated in the following sections.

The table below shows the MC Codes.

The MD code transforms integers by scaling them and inserting symbols, such as a currency sign, thousands separators, and a decimal point. The ML and MR codes are similar to MD but have greater functionality. The command syntax is:

MDn{m}{Z}{,}{$}{ix}{c}

Syntax Elements

Input Conversion works with a number that has only thousands separators and a decimal point.

Examples

Miscellaneous operators control formatting, exchanging stack entries, popping the top entry, concatenation, and string extraction. They are:

The MK code allows you to display large numbers in a minimum of columns by automatically descaling the numbers and appending a letter to represent the power of 10 used. The letters and their meanings are:

K 10 /3 (Kilo)

M 10 /6(Mega)

G 10 /9 (Giga)

The command syntax is:

MKn

Where, n represents the field width and if present will include the letter and a minus sign.

If a number will fit into the specified field width, it will not be changed.

If the number is too long but includes a decimal fraction, the MK code first attempts to round the fractional part so that the number will fit the field. If the number is still too long, the code rounds off the three low-order integer digits, replacing them with a K. If the number is still too long, the code rounds off the next three digits, replacing them with an M. If that is still too long, the code rounds off three more digits, replacing them with a G. If the number still does not fit the specified field, the code displays an asterisk.

If the field size is not specified or is zero, the code outputs null.

Input Conversion does not invert. It simply applies the metric processing to the input data.

Examples

ML and MR codes format numbers and justify the result to the left or right respectively. The codes provide the following capabilities:

• Decimal precision and scaling

• Zero suppression

• Thousands separator

• Credit codes

• Currency symbol

• Inclusion of literal character strings

The command syntax is:

ML{n{m}}{Z}{,}{c}{$}{fm}
  MR{n{m}}{Z}{,}{c}{$}{fm}

Syntax Elements

• ML provides left justification of the result.

• MR provides right justification of the result.

• n is a number from 0 to 9 that defines the decimal precision. It specifies the number of digits to be output following the decimal point. The processor inserts trailing zeros if necessary. If n is omitted or is 0, a decimal point will not be output.

• m is a number that defines the scaling factor. The source value is descaled (divided) by that power of 10. For example, if m=1, the value is divided by 10; if m=2, the value is divided by 100, and so on. If m is omitted, it is assumed to be equal to n (the decimal precision).

• Z suppresses leading zeros. Note that fractional values which have no integer will have a zero before the decimal point. If the value is zero, a null will be output.

• , is the thousands separator symbol. It specifies insertion of thousands separators every three digits to the left of the decimal point. You can change the display separator symbol by invoking the SET-THOU command. Use the SET-DEC command to specify the decimal separator.

• c appends a credit character or encloses the value in angle brackets (< >), and can be any of:

C	Print the literal CR after negative values.
D	Print the literal DB after positive values.
E	Enclose negative values in angle brackets < >
M	Print a minus sign after negative values.
N	Suppresses embedded minus sign.

If a value is negative and you have not specified one of these indicators, the value will be displayed with a leading minus sign. If you specify a credit indicator, the data will be output with either the credit characters or an equivalent number of spaces, depending on its value.

• $ specifies that a currency symbol is to be included. A floating currency symbol is placed in front of the value. The currency symbol is specified through the SET-MONEY command.

• fm specifies a format mask. A format mask can include literal characters as well as format codes. The format codes are as follows:

Code	Function
#{n}	Spaces. Repeat space n times. Output value is overlaid on the spaces created.
*{n}	Asterisk. Repeat asterisk n times. Output value is overlaid on the asterisks created.
%{n}	Zero. Repeat zeros n times. Output value is overlaid on the zeros created.
&x	Format. x can be any of the above format codes, a currency symbol, a space, or literal text. The first character following & is used as the default fill character to replace #n fields without data. Format strings may be enclosed in parentheses "( )".

The justification specified by the ML or MR code applies at different stages from that specified in field 9 of the data definition record. The sequence of events begins with the formatting of the data with the symbols, filler characters and justification (left or right) specified by the ML or MR code. The formatted data is justified according to field 9 of the definition record and overlaid on the output field, which initially comprises the number of spaces specified in field 10 of the data definition record.

Input Conversion works with a number that has only thousands separators and a decimal point.

Examples

In the last example, it ignores the leading and trailing parenthesis.

MP codes convert packed decimals to unpacked decimal representation for output or decimal values to packed decimals for input. The command syntax is:

MP

The MP code most often used as an output conversion; on input, the MP processor combines pairs of 8-bit ASCII digits into single 8-bit digits as follows:

• Strips off the high order four bits of each ASCII digit.

• Moves the low order four bits into successive halves of the stored byte

• Adds a leading zero (after the minus sign if present) if the result would otherwise yield an uneven number of halves.

• Ignores leading plus signs (+)

• Stores leading minus (-) signs as a four-bit code (D) in the upper half of the first internal digit.

When displaying packed decimal data, you should always use an MP or MX code. Raw packed data is almost certain to contain control codes that will upset the operation of most terminals and printers.

Input Conversion is valid. Generally, for selection processing you should specify MP codes in field 7 of the data definition record.

Examples

OCONV -1234 “MP”
yields 0x D01234
ICONV 0x D01234 “MP”
yields -01234

The MS code allows an alternate defined sort sequence for sort fields. The command syntax is:

MS

Use of the MS code is only relevant when applying in field 8 pre-process codes to a specified field in a sort clause. In all other cases, it will be ignored.

Use the sort sequence defined in a special record named SEQ that you must create in the ERRMSG file. Field 1 of this record contains a sequence of ASCII characters that define the order for sorting.

Example

SEQ (defined in ERRMSG file)
  001 aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyY
  zZ9876543210 ,.?!””;:+-*/^=()[]{}<>@#$%&”~\|
INV.CODE (data definition record)
  001 A
  008 MS
SORT SALES BY INV.CODE ID-SUPP
SALES....
AbC789
  ABC789
  ABC788
  dEF123

Use the MT code to convert time notations such as 01:40:30 or 1:30 AM between internal and external format. The command syntax is:

MT{H}{S}

Syntax Elements

• H specifies 12-hour format. Uses 24-hour format if omitted.

• S specifies that seconds are to be included.

Time is stored internally as the number of seconds since midnight. Outputs the stored value in 12 hour or 24 hour (international) format

Defines 12:00PM as noon and 12:00AM as midnight. Automatically displays AM and PM designators. For example, 09:40AM and 06:30PM.

Input Conversion is valid. Generally, for selection processing you should specify MT codes in field 7 of the data definition record.

Considers AM or PM designators; affects the result of the input conversion values by the time_is_hours emulation setting.

Examples for input conversion

Operators used in A code expressions include arithmetic, relational and logical operators, the concatenation operator, and the IF statement.

F code operations are typically expressed as “F;stack2;stack1;operation” and evaluated under most emulation, as “stack2 operation stack1”.

If JBCEMULATE is set to “ROS”, this example is evaluated as “stack1 operation stack2”, effectively reversing the order of operations.

No input conversion is allowed.

Example 1

F;C3;C5;-

Push a value of three onto the stack. Push a value of five onto the stack.

Take entry 1 from entry 2 (3 - 5) and push the result (-2) back onto the stack as entry 1. ROS emulations will subtract 3 from 5 and return a result of two.

Example 2

FS;C3;C5;-

Push a value of three onto the stack. Push a value of five onto the stack. Take entry 2 from entry 1 (3 - 5) and push the result (-2) back onto the stack. This works in the same way for all emulations.

Example 3

F;C2;C11;C3;-;/

Push a value of two onto the stack. Push a value of 11 onto the stack. Push a value of three onto the stack. Subtract entry 1 from entry 2 (11 - 3) and push the result (8) back onto the stack. Now divide entry 2 by entry 1 (2 divided by 8) and push the result (0) back onto the stack.

Under ROS emulation, this would evaluate as 3 - 11 = -8, followed by -8 / 2 = -4.

Output Conversion

The output specification clause names the fields that are to be included in the report.

Syntax

field {print limiter}
TOTAL field {print-limiter}
BREAK-ON field “{text}{2option{option}...’}{text}

Print limiter has the form:

 {NOT} {relational operator} “value string” {{logical-connective} {NOT} {relational-operator} “value string”}...

Syntax Elements

• TOTAL specifies that a running total of a numeric field be maintained.

• field identifies the name of a data definition record.

• print-limiter suppresses output of values (to subvalue level) that do not match the clause, which replaces suppressed values with blanks. Any detail lines that would as a result, be blank, are suppressed. Any totals produced include just the values that match the limiting clause.

• BREAK-ON specifies that control break be performed and a break line displayed, each time the value of a field changes.

• text comprises any printable characters except RETURN, LINE FEED, double quotes, single quotes or system delimiters.

• Options is one OR more of the following options:

B	Break: works in conjunction with the B option of the Heading and FOOTING modifiers to insert the break value in the heading or footing.
D	Data: suppresses the break if only one detail line has been output since the last break.
L	Line: suppresses the blankline preceding the break data line. Overrides the U option if both are specified.
P	Page: throws a new page after each new break value until all the data associated with the current break has been output.
R	Rollover: Inhibits a page break until all the data associated with the current break has been output.
U	Underlines: if specified places underlines on the line above the accumulated totals. Ignored if used with the L option.
V	Value: inserts the value of the control break field at this point in the BREAK-ON option.

If the sentence contains an output specification clause, it ignores any default definition records in the dictionary.

The P code returns a value if it matches one of the specified patterns, which can be combinations of numeric and alphabetic characters and literal strings. The command syntax is:

P{#}(element){;(element)}...

The command returns a null value if the value does not match any of the patterns.

Input Conversion does not invert. It simply applies the pattern matching to the input data.

Examples

P(2A”*”3N”/”2A)

It matches and returns AA*123/BB or xy*999/zz. It fails to match AAA*123/BB or A1*123/BB, and returns null.

P(2A”*”3N”/”2A);(2N”-“2A)

It matches and returns AA*123/BB, xy*999/zz, 99-AA or 10-xx. It fails to match AA&123/BB, A1*123/BB, 9A-AA or 101-xx, and returns null.

Field 8 codes are valid but, generally, it is easier to specify the D code in field 7 for input conversion. Dates in output format are difficult to use in selection processing.

If you are going to use selection processing and you want to use a code which reduces the date to one of its parts, such as DD (day of month), the D code must be specified in field 8.

Field 7 input and output conversions are both valid.

Generally, for selection processing, you should specify D codes in field 7 except when you use a formatting code, such as DM, that reduces the date to one of its parts. If you specify no year in the sentence, the system assumes the current year on input conversion. If specifying only the last two digits of the year the system assumes the following:

00-29       2000-2029

30-99       1930-1999

Examples

A push operator always pushes a single entry onto the stack. Existing entries are moved one position down. Push operators are: “literal” Literal. Any text string enclosed in double or single quotes.

field-number{R{R}}{(format-code)}

Where,

• field-number is the value of the field from the current record.

• R specifies that the last non-null value obtained from this field is to be applied repeatedly for each multivalue that does not exist in a corresponding part of the calculation.

• RR specifies that the last non-null value obtained from this field is to be applied repeatedly for each subvalue that does not exist in a corresponding part of the calculation.

• (format-code)** is one or more format codes (separated by value marks) enclosed in parentheses. Applied to the value before it is pushed onto the stack.

The R code returns a value that falls within one or more specified ranges. The command syntax is:

Rn,m{;n,m}...

Where,

• n is the starting integer of the range. Can be positive or negative.

• m is the ending integer of the range. Can be positive or negative, but must be equal to or greater than n.

This command returns a null value if the value does not fall within the range(s). Input Conversion does not invert. It simply applies the range check to the input data.

Examples

R1,10

Returns any value that is greater than or equal to one and less than or equal to 10.

R-10,10

Returns any value that is greater than or equal to -10 and less than or equal to 10.

R-100,-10

Returns any value that is greater than or equal to -100 and less than or equal to -10.

The fields of a file definition record that affect jQL reports are:

REFORMAT is similar to the LIST Command where it generates a formatted list of fields, but its output is directed to another file or the magnetic tape rather than to the terminal or printer.

Command Syntax

REFORMAT file-specifier {record-list} {selection-criteria} {USING file-specifier} {output-specification} {format-specification} {(options}

Prompt

At the prompt, supply the destination file:

File: 

Enter a file name, or the word “TAPE” for output to a magnetic tape.

Overwrites records that already exist in the destination file; when you reformat one file into another, each selected record becomes a record in the new file. It uses the first value specified in the output specification clause as the key for the new records. The remaining values in the output specification clause become fields in the new records.

When you reformat a file to tape, it concatenates the values specified in the output specification clause to form one tape record for each selected record. The record output is truncated or padded at the end with nulls (X”00”) to obtain a record the same length as specified when the tape was assigned by the T-ATT command.

Unless you specify HDR-SUPP or COL-HDR-SUPP, or a C or H option, a tape label containing the file name, tape record length (in hexadecimal), it will write the time, and date to the tape. If specifying a HEADING clause, this will form the data for the tape label.

Unless the ID-SUPP modifier or the 'I' option is specified record keys are displayed as the records are written to tape.

Two EOF marks terminate the file on tape.

See also the SREFORMAT Command.

Example

REFORMAT ORDER ORD.ADDR
FILE: ADDRESS

Creates new records in the ADDRESS file, keyed on C.CODE from the SALES file. Each record contains two fields, one with the values from the NAME field and one with the values from the ADDRESS field.

Relational operators specify relational operations so that any two expressions can be treated as operands and evaluated as returning true (1) or false (0). Relational operators are:

Relational operators also compare stack entries and push the result onto stack entry 1; is either 1 (true) or 0 (false). Relational operators are:

The remainder function R(exp1, exp2) takes two expressions as operands and returns the remainder when dividing the first expression by the second. For example,

A;R(N(HOURS) / “24”) - Returns the remainders when 24 divide HOURS.

The summation Function S(expression) evaluates an expression and then adds together all the values. For example,

A;S(N(HOURS) * N(RATE)R)

Multiplies each value in the HOURS field by the value of RATE. The multivalued list of results is then totalled.

To repeat a value for combination with multivalues, follow the field number with the R operator. To repeat a value for combination with multiple subvalues, follow the FMC with the RR operator.

Some MC codes replace one set of characters with other characters. These codes can:

• Exchange one character string for another

• Replace non-printable characters with a marker character

• Replace non-printable characters with a marker character and the character’s hexadecimal representation

• Replace the marker and hexadecimal representation with the ASCII code

Input conversion does not invert. The original code will be applied to input data.

Examples

MCC;X5X;YYY

Assume a source value of ABC*X5X!DEF. In this case, MCC will return ABC*YYY!DEF.

MCPN

Assume a source value of ABC]]DEF where ] represents a value mark. In this case, MCPN will return ABC.FC.FCDEF.

The S code substitutes one value for another. The command syntax is:

S;Var1;Var2

Where,

• Var1 specifies the value to be substituted if the referenced value is not null or zero. It can be a quoted string, an FMC (field number), or an asterisk. An asterisk indicates that you should use the value of the referenced field.

• Var2 specifies the value for substitution if the referenced value is null or zero.  It can be a quoted string, an FMC (field number), or an asterisk.

Examples

S;*;”NULL VALUE!”

If the referenced field is null, this example will return the string “NULL VALUE!”. Else, it will return the referenced value.

S;*;3

If the referenced field is null, this example will return the content of field 3 of the data record. Else, it will return the referenced value.

S;4;5

If the referenced field is null, this example will return the content of field 5 of the data record. Else, it will return the content of field 4.

This command generates an implicit list of record keys or specified fields based on the specified selection criteria. The command syntax is:

SELECT file-specifier {record-list} {selection-criteria} {sort-criteria} {output-criteria} {USING file-specifier} {(options}

Where options are,

• C{n} displays running counters of the number of records selected and records processed. Unless modified by n, the counter increments after every 500 records processed or the total number of records if less than 500.

• n specifies a number other than 500 by which to increment. For example, C25 increments the counter after every 25 records processed.

You must specify a sort criteria clause to sort records.

See also the SSELECT Command.

If you specify an output-criteria clause, the generated list will comprise the data (field) values defined by the clause, rather than the selected record keys.

If you are in jSHELL when the Command terminates, it displays the total number of entries in the generated list and the list is made available to the next Command, as indicated by the > prompt.

If you use the BY-EXP or BY-EXP-DSND connectives on a multivalued field, the list will have the format:

record-key]multivalue#

Where multivalue# is the position of the multivalue within the field specified by BY-EXP or BY-EXP-DSND. multivalue# can be accessed by a READNEXT Var,n statement in a jBC program.

Examples

SELECT ORDER WITH ORD.AMT = “ABC]”
23 Records selected
>LIST ORDER WITH VALUE > “1000”

Select all the records in ORDER file with an ORD.AMT value that starts with ABC. Then, using the list, report on the records in the ORDER file which have a VALUE field greater than 1000.

SELECT ORDER WITH ORD.AMT = “ABC]”
23 Records selected
>SAVE-LIST ORDER.ABC

Select all the records in ORDER file with an ORD.AMT value that starts with ABC. Then save the list as ORDER.ABC.

Values string delimiters are single quote (‘) and double quote (“). You can enclose an item-id value string in double quotes, but only if it is entered immediately after the file name. Use single quotes within item-id selection clauses and doubles quotes within ordinary selection criteria except when you are searching for an item-id that includes single quotes.

Outputs data in a format suitable for producing labels. The command syntax is:

SORT-LABEL file-specifier {record-list} {selection-criteria} {sort-criteria} {USING file-specifier}{output-specification} {format-specification} {(options}

Prompts

At the prompt, supply formatting criteria as follows:

COL, ROW, SKIP, INDENT, SIZE, SPACE(,C):

The total number of columns specified must not exceed the page width, based on the calculation:

COLs * (SIZE + SPACE) + INDENT <= page width

ROW must be a minimum of one for each field, plus one for the record key (if not suppressed). If record keys are not suppressed, the first row of each label will contain the record key. If you specify a sort criteria clause, it sorts the records in key order.

If INDENT is not zero, at the prompt supply a series of HEADERs that will appear in the left margin for each field. If a heading is not required for a particular line, press RETURN.

Multivalued fields appear on separate lines.

If specified, COL-HDR-SUPP or HDR-SUPP, or the C or H options, the page number, date, and time will not be output and the generated report will be without page breaks.

The sort criteria clause allows you to specify the presentation order of the records in the report.

Syntax

BY field
BY-DSND field
BY-EXP field {print-limiter}
BY-EXP-DSND field {print limiter}

Print limiter has the form:

{relational operator} “value string” {{logical connective} {relational operator} “value string”}...

Where,

Each sort clause comprises a sort connective followed by a field name. The sort connective can specify an ascending or descending sort sequence of single or multivalued fields. If you include more than one sort of criteria clause, the processor ranks the clauses in a left to right, most to least important hierarchical sequence. Always used as the least important sort value, unless explicitly included in the SORT criteria is the record key.

The precise sorting sequence depends on whether a field is left - right justified.

Default Sort Order

If you do not specify a sort criteria clause for a sorting command, the report is output in ascending order by record key. Field 9 of the file definition (pointer) record specifies left or right justification of the key. The default is a left justified sort.

Sort Order of Left Justified Data

When sorting a left justified field, the data is compared one character at a time, left to right. For this reason number two will follow number 11 in an ascending sequence. The number 02 would appear before 11 in an ascending sequence.

The sort connectives for single valued fields sort the record orders according to the value of a field.

The two sort connectives for single value fields are:

• BY for an ascending sort

• BY-DSND for a descending sort

If using a single value sort connective with a field that contains multivalues or subvalues, it only uses the first value in the field as the sort key.

Sorting Multivalued Fields

The sort connectives for multivalued fields sort values within a field. The two sort connectives for multivalues are:

• BY-EXP for ascending order

• BY-EXP-DSND for descending order

If using a multiple value sort connective with a file, which contains subvalues, it only uses the first subvalue in each multivalue as the sort key. The treatment of each value is as if it were the only value so that each value occupies a line of output in the report. This effectively “explodes” a record into multiple records. You can limit the values for sorting and output by including a print limiter with the multivalue sort connectives.

When using a SELECT-type command with BY-EXP the formatting of the records list appears:

record-key]multi value#

Where, multi-value-# is the position of the multivalue within the field. The READNEXT statement in a jBASIC program can use this value.

Example 1

SORT SALESORDER WITH S.CODE = “ABC]”ORD.COST => ‘500’ BY S.CODE
ORD.COST  

Selects the records in the ORDER file in which the ORD.COST file contains the values of the order and must sort the orders greater than or equal to 500. The output in the records is in ORD.COST order.

Example 2

SORT ORDER WITH ORD.COST = ‘500’ BY ORD.COST BY-DSND ORD.ID

Example 3

SORT ORDER BY-EXP ORD.ID

Selects all the records in the Order file and outputs the detail lines in key order within P.CODE order.

This command generates a sorted and formatted report of records and fields from a specified file. The command syntax is:

SORT file-specifier {record-list} {selection-criteria} {sort-criteria} {USING file-specifier} {output-specification} {format-specification} {(options}

Unless a different sort order is specified in the sort criteria, the presentation of the records will be in an ascending order based on the record key.

The data definition records (or the file definition records in the case of keys) determine whether to apply a left or right sort to the data.

If the field is left justified, it compares the data on a character-by-character basis from left to right, using ASCII values. For example,

01
 100
 21
 A
 ABC
 BA

If the field is right justified and the data is numeric, it performs a numeric comparison and the values ordered by magnitude.

If the field is right justified and the data is alphanumeric, it collates the data into an alphanumeric sequence.

Another example:

A
 01
 123
 ABCD

If a descending sequence is required, use the BY-DSND modifier in the sort criteria. Use the BY-DSND modifier with a data definition record to obtain a descending sequence of record keys, which points to field 0 (the key). See “Sort Criteria Clause” earlier for a full explanation of the sorting process.

Example 1

SORT ORDER

Sorts all the records in the SALES file into key order and use the default data definition records (if found) to format the output.

Example 2

SORT ORDER WITH ORD.AMT = “ABC” “DEF” “GHI”

Selects the records in the ORDER file in which the ORD.AMT field contains the values ABC, DEF or GHI. Sort the records into key order.

Example 3

GET-LIST SALES.Q4
SORT ORDER GT “DEF” BY ORD.AMT

Gets the implicit list called SALES.Q4 and, using the list, report on the records in the SALES file, which have a key greater than DEF. Sort does the report by S.CODE.

Example 4

SORT ORDER WITH ORD.AMT = “ABC]” OR “[DEF” BY-DSND S.KEY LPTR

Selects the records in the SALES file in which the S.CODE field contains values which start with ABC or end with DEF. Sort the report in descending order of S.KEY (a data definition record which points to field 0 - the key) and output the report to the printer

Example 5

SORT ORDER BY ORD.ID BREAK-ON ORD.ID “”BL” ORD.AMT TOTAL ORD.COS GRAND-TOTAL “Total” HEADING “Sales Code: “B” “DL” FOOTING “Page “CPP” LPTR

Sorts the ORDER file by ORD.ID. Output the ORD.ID, ORD.AMT and VALUE fields.

Control break on a change in S.CODE and suppress the LINE FEED before the break. Reserve the break value for use in the heading (“B”). Maintain a running total of the VALUE field and output it at each control break. Put the word “Total” on the grand-total line.

Set up a heading for each page which comprises the words “Sales Code: “, the sales code (from the break), a date and a LINE FEED. Set up a footing, which contains the text “Page”, and a page number, centered on the line?

Produce the report on the currently assigned printer.

SREFORMAT is similar to the SORT command where it generates a formatted list of fields, but directs its output to another file or the magnetic tape rather than to the terminal or printer. The command syntax is:

SREFORMAT file-specifier {record-list} {selection-criteria} {USING file-specifier} {output-specification} {format-specification} {(options}

Prompt: At the prompt supply the destination file:

File: Enter a file name, or the word “TAPE” for output to a magnetic tape.

This command overwrites records that already exist in the destination file; when you reformat one file into another, each record selected becomes a record in the new file. It uses the first value specified in the output specification clause as the key for the new records. The remaining values in the output specification clause become fields in the new records.

When you reformat a file to tape, it concatenates the values specified in the output specification clause to form one tape record for each selected record. The record output is either truncated or padded at the end with nulls (X”00”) to obtain a record the same length as specified when the tape was assigned by the T-ATT Command.

Unless you specify HDR-SUPP or COL-HDR-SUPP, or a C or H option, a tape label containing the file name, tape record length (in hexadecimal), it first writes the time, and date to the tape. If specifying a HEADING clause, this will form the data for the tape label.

Record keys are displayed as the records are written to tape unless the ID-SUPP modifier or the “I” option is specified.

Two EOF marks terminate the file on tape.

See the REFORMAT command for examples.

This command generates an implicit list of record keys or specified fields, based on the selection criteria specified. The command syntax is:

SSELECT file-specifier {record-list} {selection-criteria} {sort-criteria} {output-criteria}
{USING file-specifier} {(options}

Where options are:

Unless you specify a sort criteria clause, it sorts the records in key order. See also the SELECT command.

If you specify an output-criteria clause, the generated list will comprise the data (field) values defined by the clause, rather than the selected record keys.

When the command terminates, it displays the total number of entries in the generated list. The list is available to the next command. This is indicated by the “>” prompt if you are in jSHELL.

If you use the BY-EXP or BY-EXP-DSND connectives on a multivalued field, the list will have the format:

record-key]multivalue#

Where multivalue# is the position of the multivalue within the field specified by BY-EXP or BY-EXP-DSND. It can be accessed by a READNEXT Var,n statement in a jBASIC program.

Examples

SSELECT ORDER WITH ORD.AMT = ‘100’
23 Records selected
LIST ORDER WITH ORD.QTY > ‘1000’

Select all the records in SALES file with an S.CODE value that starts with ABC. Sort the list into key order. Then, using the list report on the records in the SALES file which have a VALUE field greater than 1000.

SSELECT ORDER WITH ORD.AMT = “ABC]” BY P.CODE
23 Records selected
>SAVE-LIST SALES.ABC

Select all the records in ORDER file with an ORD.AMT value that starts with ABC. Sort the list into ORD.AMT order and then save the list as SALES.ABC.

Use the COUNT and LIST commands to access file records which contain sublists, with the COUNT and LIST Commands. For the commands and the modifier to function correctly, you must include the V processing code in field 8 of the file definition record. See File Specifiers topic in the jQL Sentence Construction Chapter for more details. The command syntax is:

V;field-no

Where, field-no specifies the number of the field, which contains the sublist.

Example

Consider the stock file used by a camera factory where each data record can represent either an assembly or a component part. For example, the record set that defines a simple camera assembly. The data records contain the following data.

The key is the part number, field 1 contains the description, field 2 is a multivalued list of components that go to make up the part, and field 3 is the current stock level.

Record A1 represents assembled cameras. It points to the used sub-assemblies (A21, A22 and A23) to make each camera. The sub-assemblies in turn point to their component parts; A21 points to A210 and A211, A22 does not have any components, and A23 points to A230.

Having established the logical data relationships, we now need to ensure that the system understands that field 2 is a multivalued sublist. We do this by updating field 8 in the file definition record to read “V;;2”,

like this:

STOCK
  001 D
  002
  003
  004
  005
  006
  007
  008 V;;2
  009 L
  010 10

To create three data definition records in the dictionary of STOCK - one for each field, use the following titles DESC, COMPONENTS, and QTY.

The final step is to issue a COUNT or LIST command, which uses the WITHIN modifier:

LIST WITHIN STOCK “A1” DESC COMPONENTS QTY
 
PAGE 1 Time Date
 
LEVEL STOCK Description.... Components Qty
 
  1 A1 CAMERA ASSY A21 10
  A22
  A23
  2 A21 LENS ASSY A210 15
  A211
  3 A210 OPTICS 19
  3 A211 BARREL 21
  2 A22 BODY 10
  2 A23 SHUTTER ASSY A230 11
  A231
  3 A230 IRIS MECH 13
  3 A231 FILM MECH 14
 
8 RECORDS LISTED

The substring function [start-char-no, len] extracts the specified number of characters from a string, starting at a specified character.

Where,

• start-char no – An expression that evaluates to the position of the first character of the substring.

• len – An expression that evaluates to the number of characters required in the substring.          

Use - len (minus prefix) to specify the end of the substring. For example, [1, -2] will return all but the last character and [-3, 3] will return the last three characters.

Examples

A;N(S.CODE)[“2”, “3”]

Extracts a sub-string from the S.CODE field, starting at character position 2 and continuing for 3 characters        

A;N(S.CODE)[2, N(SUB.CODE.LEN)]

Extracts a sub-string from the S.CODE field, starting at the character position defined by field 2 and continuing for the number of characters defined by SUB.CODE.LEN.

Format Codes: Specifies a format code to be applied to the result of the A code or an operand.

Command Syntax

a-code{]format-code...}
 a-operand(format-code{]format-code}...)

Syntax Elements

• a-code – A complete A Code expression.

• a-operand – One of the A Code operands.

• format-code is one of the codes described later G(roup), D(ate) or M(ask).

• ] – Represents a must use value mark to separate each format code.

You can format the result of the complete "A" code operation by following the expression with a value mark and then the required format codes (this is a standard feature of the data definition records).

Format codes can also be included within "A" code expressions enclosed in parentheses, using a value mark for separation if using more than one format code. All format codes will convert values from an internal format to an output format.

Example 1

A;N(COST)(MD2]G0.1) * ...

Shows two format codes applied within an expression. Obtains the COST value and applies an MD2 format code. Then applies a group extract to acquire the integer portion of the formatted value. You can now use the integer portion in the rest of the calculation. This can also be achieved like below:

A;I(N(COST)(MD2)) * ...

Example 2

A;N(COST) * N(QTY)]MD2

Shows the MD2 format code applied outside the A code expression. Multiplies COST by QTY and the result formatted by the MD2 format code.

Operands which you can use in "A" code expressions include:

• FMCs (field numbers)

• field names

• literals

• operands that return system parameters,

You can format any operand by following it with one or more format codes enclosed in parentheses and separated by value marks, (ctrl ]):

operand(format-code{]format-code}...)

For more information, see Format Codes.

The T code extracts a character substring from a field value. The command syntax is:

T{m,}n

Where,

• m specifies the starting column number.

• n the number of characters for extraction.

If specifying m, the content of field 9 of the data definition record has no effect. It counts and extracts characters from left to right, for n characters.

If m is not specified, then the content of field 9 of the data definition record will control whether n characters are extracted from the left or the right-hand end of the value. If field 9 does not contain an R, extracts the first n characters from the value. If field 9 does contain an R (right justify), extracts the last n characters from the value.

Input Conversion does not invert. It simply applies the text extraction to the input data.

Examples

Tfile codes provide a method for retrieving data fields from any other file to which the user has access. The command syntax is :

T[*|DICT]file-specifier;c{n};{i-fmc};{o-fmc}

Where,

Uses the current data value as the record key for searching the specified reference file. Returns a data field or a single value from a data field, from the record.

Use Tfile codes in fields 7 or 8 of the data definition record. Use field 8 if translation of a multivalued field or comparisons and sorts are required.

If you apply selection criteria, you can either use field 8, or field 7 and set up special records in the reference file to perform any input translation you require.

The special records in the reference file have as record keys values that the field subject to translation may be compared with in a jQL sentence. Field i-fmc within these records contains the translate value that will be compared to values on file. Typically, values in a jQL sentence are output values, so that the special input translation records are effectively the inverse of the output translation records.

Tfile codes can be “embedded” in other conversion codes but you must still follow the syntactical conventions of the “host” code. For Example, if you include a Tfile code in an F code conversion, enclose the Tfile code in parentheses.

Output conversion is valid. The Tfile code has a parameter (o-fmc) that specifies the field in the translation record to use for output conversion.

Input conversion is valid. The Tfile code has a parameter (i-fmc) that specifies the field in the translation record to use for input conversion.

Examples

TSALES;X;;2

Using this Tfile code in field 8 of a data definition record, which also has a 0 in field 2, will cause the key of the current record to be used as the key when accessing the reference file SALES; returns null if the record cannot be found; returns the value of field 2 if the record is found.

TSALES;C;;2

Using this Tfile code in field 8 of a data definition record, which also has a 6 in field 2, will cause the content of field 6 from the current record to be used as the key when accessing the reference file SALES. If the record cannot be found, or if found, field two is null, returns the content of field 6 of the current record. If the record is found, and field 2 contains a value, it returns that value.

A;3(TSALES;X;;2)

Using this embedded Tfile code in field 8 of a data definition record will cause the use of field 3 of the current record as the key when accessing field 2 of the reference file SALES. Returns null if the record cannot be found; returns the value of field 2 if the record is found.

F codes work with a pushdown stack.

The term “push” is used to indicate the placing of an entry (a value) onto the top of the stack so that existing entries are pushed down one level. “Pop” means to remove an entry from the top of the stack so that existing entries pop up by one level. Arithmetic functions typically begin by pushing two or more entries onto the stack. Each operation then pops the top two entries, and pushes the result back onto the top of the stack. After any operation is complete, the result will always be contained in entry 1.

Throwaway connectives are keywords, which make queries more readable. You can use in any query to make the sentence read more like English and can be used anywhere in a sentence as throwaway connectives do not affect the query.

The following query uses the words THE, FOR, and FILE without affecting the meaning of the command:

LIST THE ORDER BY ORD.UNIT.PRICE FOR THE ORD.STATUS

The throwaway keywords are:

For timestamp functionality a suite of conversions is included like A, F and I types. This is to generate a timestamp, displayed for date and/or time in short, long, and full formats. These conversions also support non-Gregorian locales. The meaning of the components of the conversion is as follows:

“WDS” or “WTS” SHORT is completely numeric.12/13/52 or 3:30pm

“WDM” MEDIUM is longer. Jan 12, 1952

“WDL” or “WTL” LONG is longer.

January 12, 1952 or 3:30:32pm

“WDF” or “WTF” FULL is specified completely.

The TOTAL connective specifies that a running total of the field be maintained and to output the total at each control break and at the end of the report. Also, use TOTAL in conjunction with the BREAK-ON connective to display intermediate totals at the control breaks.

Use the GRAND-TOTAL modifier in the format specification clause to display any specified text on the last total line.

An item list takes one of three forms:

1. An explicit item-id list

2. An implicit item-id list

3. An item-id selection clause

You can combine Item-id selection with implicit but not with explicit item-id lists. You can combine every type of list with selection criteria based on attribute values.

Use the U code to execute a system subroutine to process values. The command syntax is:

Uxxxx

Where,

• xxxx – The hexadecimal identity of the routine.

jBASE user exits are customized routines specially produced to perform extraordinary processing.

Input Conversion: Routine dependent

Unicode is a single-coded character set providing a repertoire for all the languages of the world. Its first version used 16-bit numbers, which allowed encoding for 65,536 characters; further development allowed a repertoire of more than one million characters, requiring 21 bits. Higher bits have been declared unusable to ensure interoperability between UTF encoding schemes; UTF16 cannot encode any code points above this limit. The handling of values above 16 bits is by two 16-bit codes.

• The first Unicode version used 16 bits, which allowed for encoding 65,536 characters.

• Further extended to 32 bits, although restricted to 21 bits to ensure interoperability between UTF encoding schemes.

• Unicode provides a repertoire of more than one million characters.

The 16-bit subset of UCS (Universe Character Set) is known as the Basic Multilingual Plan (BMP) or Plane 0.

Unicode provides a unique number for every character, on every platform, for every program, no matter what the language. Standards such as XML, Java, ECMAScript (JavaScript), LDAP, CORBA 3.0, WML, etc., requires Unicode and is the official way to implement ISO/IEC 10646; supported in many operating systems, all modern browsers, and many other products.

Incorporating Unicode into client-server or multi-tiered applications and websites can offer significant cost savings over the use of legacy character sets. Unicode enables a single software product or a single website to be targeted across multiple platforms, languages and countries without re-engineering, and allows data to be transported through many different systems without corruption.

Unicode Combining/Composite Characters

Some code points have been assigned to combining characters. A combining character is not a full character by itself; rather, it is an accent or other diacritical mark added to the previous character. This makes it possible to add an accent or other on any character. This capability is particularly important for scientific notation in mathematics.

Unicode Expanding and Contracting Characters

Some languages treat certain single characters as two separate characters for collating purposes (expanding), whereas some languages also treat two characters as a single character when collating (contracting).

Contracting in Spanish sort order, ‘ch’ is considered a single letter. All words that begin with ‘ch’ sort after all other words beginning with ‘c’.

Expanding in German, ä is equivalent to ‘ae,’ such that words beginning with ä sort between words starting with ‘ad’ and ‘af’.

Unicode Normalization

Normalization is the removal of ambiguities caused by precomposed and compatibility characters. There are four different forms of normalization.

Form D – Split up (decompose) precomposed characters into combining sequences where possible.

Form C – Use precomposed instead of combining sequences where possible.

Form NFKD – Like D but avoid use of compatibility characters (e.g., use ‘fi’ instead of U+FB01 LATIN SMALL LIGATURE FI).

Form NFKC – Like C but avoid use of compatibility characters.

Below is an example of Precomposed vs. Decomposed characters.

Precomposed               ü = U+00FC

Decomposed                ü  = U+0075 + U+0308

UTF-8 encoding requires the use of precomposed characters wherever possible.

Unicode Universal Character Set and UTF-8

The Universal Character Set, UCS assigns each character not only a code number, but also an official name. A U+ precedes a hexadecimal number representing a Unicode Code Point or (e.g., U+0041 ‘Latin Capital Letter A’).

UTF-8 can represent all possible Unicode code points by byte sequences, which in turn represent different code points. The sequence used for any given character depends on the Unicode number, which represents that particular character. The Universal Character Set has the following properties:

• UCS U+0000 to U+007F is identical to US-ASCII (IISO 646 IRV).

• UCS U+0000 to U+00FF are identical to ISO-8859-1 (Latin-1)

• UCS U+E000 to U+F8FF is reserved for private use.

UTF-8 encoding is a Unicode Translation Format of Unicode. Before UTF-8 emerged, users all over the world had to use various language-specific extensions of ASCII. This made the exchange of files difficult, and application software had to consider small differences between these encodings. Support for these encodings was usually incomplete and unsatisfactory, because the application developers rarely used all these encodings themselves.

Unicode UTF-8 Properties

UTF-8 encoding avoids several problems inherent in other encoding systems:

• Files and strings that contain only 7-bit ASCII characters have identical encoding under ASCII and UTF-8.

• ASCII bytes 0x00-0x7F cannot appear as part of any other character.

• Allows easy resynchronization and makes the encoding stateless and guards against the possibility of missing bytes.

• Can encode all possible 231 UCS codes.

• UTF-8 encoded characters may theoretically be up to six bytes long, however 16-bit BMP characters are only up to three bytes long.

• The sorting order of Bigendian UCS-4 byte strings is preserved.

• It never uses the byte 0xFE and 0xFF in the UTF-8 encoding.

• UTF-8 is also much more compact than other encoding options, because characters in the range 0x00-0x7f still only use one byte.

• Use only the shortest possible multi byte sequence that can represent the code point of the character.

• In multi byte sequences, the number of leading one bit in the first byte is identical to the number of bytes in the entire sequence.

• Unicode represents a unique character by a unique 32-bit integer. Hence using UTF-8 encoding avoids problems, which would arise if using 16 or 32 bit character byte streams, as the normal C string termination byte is a zero, thus byte streams could become prematurely truncated.

You can add additional functionality by calling user written basic subroutines, which you should compile and catalog and add the library location to the library path in the JBCOBECTLIST environment variables.

The first parameter of the called routine is the result parameter, which is used as the evaluated value of the subroutine. For example,

FRED
  001 SUBROUTINE FRED(Result, Param1)
  002 IF Param1 > 100 THEN Result = 1 ELSE Result = 0
  003 RETURN

One or other of the following formats can call subroutines from an I-TYPE.

FRED(param1 {,param2_}) or SUBR(“FRED”,param1 {,param2_})

Conversion

The Conversion attribute provides support for normal queries output conversions. For example, D2, MT, F;, TFile, etc.

Header

This attribute specifies the column heading text for display.

Format

The format attribute can be specified as follows:

Length {Padding} Justification { Conversion } { Mask }

Where,

Mask Output pattern example ##-###-##

The using clause specifies a dictionary file, which is the source of data definition records.

Syntax

USING {DICT} filename {,data-section-name}

Where,

• USING specifies the use of the named file as the dictionary for the data file.

• DICT specifies that the dictionary of filenames be used

• filename names a file. If the DICT modifier is not specified, it will use the data section of the file.

• data-section-name specifies a secondary data section of the file with a name different from the dictionary; it must follow filename, separated by a comma but no space.

There can be only one USING clause in a jQL sentence. One main advantage of the using clause is that you can share a dictionary between several files where for example there are common data definition records.

Example

SORT ORDER USING DICT ORDER

The data definition records in the dictionary of the file ORDER (DICT ORDER) assess File ORDER.

Report qualifiers provide a variety of ways to control and refine the overall format of a report. COL-HDG, ID-SUP, DET-SUP, LPTR, SAMPLE, and SAMPLED are report qualifiers you saw in previous examples. The following list summarizes the most commonly used report qualifiers:

Value strings are character strings enclosed in delimiters (usually single quotes within item-id-selection criteria and double quotes within ordinary selection criteria). These string are also used to compare against character strings in the file. The value string cannot contain the character by which it is delimited. For example, if the value string is enclosed in single quotes, it may contain double quotes, but not single quotes. Otherwise, the value string can contain any printable character, excluding RETURN, LINE FEED, and system delimiters. The simplest value string is a character string that has precisely those characters for testing (for example. ‘Johansen’) however, a value string can also include the following special characters:

• Left ignore ([) at the beginning of the string to indicate that the item-id may start with any characters (for example,’[son’)

• Right ignore (]) at the end to indicate that the item-id may end with any characters (for example, Johan]’)

• Wild cards(^) anywhere within the string, each wild card matching one character of any value (for example, ‘Joh^ns^n).

Example

The following sentence lists CUSTOMER information with CUSTOMER numbers “40823” or “40825”.

LIST CUSTOMER = ‘40823’ ‘40825’

The following sentence list information about all the rooms with numbers that begin with three, end with five, and have an intervening character of any value.

LIST CUSTOMER = ‘3^5’

The following sentence does not list any CUSTOMER because there is no relational operator, the string 3^5 is treated as an item-id.

LIST CUSTOMER ‘3^5’