jBase Query Languge Conversion Codes

Conversion Processing

A	Algebraic functions.
B	Subroutine call.
C	Concatenation.
D	Internal and external dates.
D1 and D2	Associates controlling and dependent fields.
F	Mathematical functions.
G	Group extract.
L	Length.
MC	Mask character.
MD	Mask decimal.
MK	Mask metric.
ML and MR	Mask with justification.
MP	Mask packed decimal.
MS	Mask Sequence.
MT	Mask time.
P	Pattern match.
R	Range check.
S	Substitution.
T	Text extraction.
TFILE	File translation.
U	User exit.
W	Timestamps.
JBCUserConversions	How to create user-defined conversion codes

jQL Dictionary Conversions and Correlatives

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.

TimeStamp "W{Dx}{Tx}"

In addition, to provide for timestamp functionality included is a suite of conversions including 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:
W => Is a new conversion code so not to clash with existing conversions.
D => Date
T - => Time
x => Format option: S = Short, M = Medium, L = Long, F = Full

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

Data Conversion

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 used to represent LATIN SMALL LETTER A WITH GRAVE
Unicode value 0x0155 used to represent LATIN SMALL LETTER R WITH ACUTE
NOTE: that UTF-8 is an encoding of 32 bit Unicode values, which also has especially properties (as described earlier), which can be used effectively with Unix and Windows platforms.
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/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.

Continue Reading...

jBase Query Language JQLPUTPROPERTY

Sets a property in a compiled jQL statement

Command Syntax

JQLPUTPROPERTY(PropertyValue, Statement, Column, PropertyName)

Syntax Elements

PropertyValue is the value you want to which to set the specified property, such as 1 or "BLAH"
Statement is the result of a valid JQLCOMPILE() function.
Note: 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 INCLUDE'ing the file JQLINTERFACE.h. There are lots of these and someone is going to have to document each one.

This function returns -1 if a problem was found in the statement and 0 if there was not.

NOTE: Properties are valid after the compile; this is the main reason for separating the compile and execute into two functions. After compiling, it is possible examine the properties and set properties before executing.

Continue Reading...

jBase Query Language JQLGETPROPERTY

Gets the property of a compiled jQL statement.

Command Syntax

JQLGETPROPERTY(PropertyValue, Statement, Column, PropertyName)

Syntax Elements

PropertyValue : Receives the requested property value from the system or "" if the property is not set.
Statement: The result of a valid JQLCOMPILE(Statement).
Column : Specifies that you want the value of the property for a specific column (otherwise 0 for the whole statement).
PropertyName : These are EQUATED values defined by INCLUDE'ing the file JQLINTERFACE.h. This function returns -1 if there is a problem with the parameters or the programmer. The use of these properties is to answer questions such as "Was LPTR mode asked for," and "How many columns are there?"

NOTE: Properties are valid after the compile; this is the main reason for separating the compile and execute into two functions. After compiling, it is possible examine the properties and set properties before executing.

Continue Reading...

jBase Query Language JQLFETCH

JQLFETCH fetches the next result in a compiled jQL statement.

Command Syntax

JQLFETCH(Statement, ControlVar, DataVar)

Syntax Elemets

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

NOTE: that column headers may also affect the formatting for that column.

This function is called multiple times until there is no more output.

Continue Reading...

jBase Query Language JQLEXECUTE

JQLEXECUTE starts executing a compiled jQL statement.

Command Syntax

JQLEXECUTE(Statement, SelectVar)

Syntax Elements

Statement is the valid result of a call to JQLCOMPILE(Statement, …).
SelectVar is a valid select list that used to limit the statement to a predefined set of items.

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.

Continue Reading...

jBase Query Language JQLCOMPILE

JQLCOMPILE compiles a jQL statement.

Command Syntax

JQLCOMPILE(Statement, Command, Options, Messages)

Synatax Elements

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.

Option: To supply a select list to the JQLEXECUTE function specify JQLOPT_USE_SELECT; 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.

Continue Reading...

jBase Query Language REFORMAT

REFORMAT is similar to the LIST Command in that 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.
Comments: 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 SALES C.CODE NAME ADDRESS 
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.

Continue Reading...

jBase Query Language SELECT

Generates an implicit list of record keys or specified fields based on the specified selection criteria.

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

Syntax Elements

C{n} Display 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.
nn Specifies a number other than 500 by which to increment. For Example, C25 increments the counter after every 25 records processed.
Comments: 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 jBASIC program.

Example 1

SELECT SALES WITH S.CODE = "ABC]" 
23 Records selected
>LIST SALES WITH VALUE > "1000"

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

Example 2

SELECT SALES WITH S.CODE = "ABC]" 
23 Records selected 
>SAVE-LIST SALES.ABC

Select all the records in SALES file with an S.CODE value that starts with ABC. Then save the list as SALES.ABC.

Continue Reading...

jBase Query Language SSELECT

Generates an implicit list of record keys or specified fields, based on the selection criteria specified.

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

Syntax Elements

C{n} Display 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.

Comments: 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. multivalue# can be accessed by a READNEXT Var,n statement in a jBASIC program.

Example 1

SSELECT SALES WITH S.CODE = "ABC]" 
23 Records selected 
LIST SALES WITH VALUE > "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.

Example 2

SSELECT SALES WITH S.CODE = "ABC]" BY P.CODE
23 Records selected
SAVE-LIST SALES.ABC

Select all the records in SALES file with an S.CODE value that starts with ABC. Sort the list into P.CODE order and then save the list as SALES.ABC.

Continue Reading...

jBase Query Language SREFORMAT

SREFORMAT is similar to the SORT Command in that 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.

Command Syntax

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

File: Enter a file name, or the word “TAPE” for output to a magnetic tape.
Comments: 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.

Continue Reading...

jBase Query Language SORT-LABEL

Outputs data in a format suitable for producing labels.

Command Syntax

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):

COL	Number of columns required to list the data across the page.
ROW	Number of lines for each record; the output of each element of the output specification is on a separate line, if more elements exist in the output specification than there are rows specified it ignores the extra elements. If specifying more rows than elements, the output specification for these rows will be blank.
SKIP	Number of blank lines between each record.
INDENT	Number of spaces for left margin.
SIZE	Number of spaces required for the data under each column
SPACE	Number of horizontal spaces to skip between columns.
C	Optional. Suppresses null or missing data. If absent, null or missing values are output as blanks. If present, the C must be upper case and not in quotes.

Comments: 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.
See also the LIST-LABEL Command.

Continue Reading...

jBase Query Language LIST-LABEL

Outputs data in a format suitable for producing labels.

Command Syantax

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

Prompt

At the prompt, supply formatting criteria as follows:

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

COL	The number of columns required to list the data across the page.
ROW	Number of lines for each record; Each element of the output specification appears on a separate line, if more elements exist in the output specification than rows specified it ignores the extra elements. If you specify more rows than elements, the output specification for these rows will be blank.
SKIP	Number of blank lines between each record.
INDENT	Number of spaces for left margin.
SIZE	Number of spaces required for the data under each column.
SPACE	Number of horizontal spaces to skip between columns
C	Optional: Suppresses null or missing data; If absent, outputs null or missing values as blanks. If present, the C must be upper case and not in quotes

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

See also the SORT-LABEL Command.

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

Output of above cannot be shown here correctly

Continue Reading...

jBase Query Language I-DUMP / S-DUMP

Displays the entire contents of items in a file, including the system delimiters.

Command Syntax

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

Comments: Use the S-DUMP Command to produce sorted output.
Denoted as follows are system delimiters:

Attribute mark => ^
Value mark => ]
Sub value mark => \

Examples

jsh machinename ~ -->I-DUMP CUSTOMER WITH CUS.CITY ="BEAVERTON"

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

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

output as follows

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

Continue Reading...

jBase Query Language ESEARCH

Generates an implicit list of records in a file if they contain (or do not contain) one or more occurrences of specified character strings.

Command Syntax

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

Syntax Elements

Options can be one or more of the following:

Options	Description
A	ANDs prompted strings together. Records must contain all specified strings.
I	ADisplays the keys of selected records.
L	Saves the field numbers in which it found the specified strings. The resulting list contains the record keys followed by multivalued line numbers. Ignores the A and N options if either or both are specified.
N	Selects only those records that do not contain the specified string(s).
S	Suppresses the list but displays the record keys that would have been selected.

Prompt : At the prompt supply one or more search strings:
String : Enter the required character string and press . This prompt is repeated until only is pressed. You can enter unlimited characters.
Do not enter double quotes unless they are part of the string to search.
Comments : 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 SALES (I 
STRING: ABC 
STRING: DEF 
KEY1
KEY2

2 Records selected
Generates a list of all records in the SALES file, which contain the strings ABC or DEF .

Continue Reading...

jbase Query Language EDELETE

Deletes selected records from a file according to record list or selection criteria clauses.

Command Syntax

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

Comments : 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 SALES "ABC" "DEF"

2 Records deleted
Delete the records ABC and DEF based on the explicit list of records.

EDELETE SALES IF P.CODE = "GHI]"

n Records deleted
Delete all records in the SALES file in which the P.CODE field starts with GHI.

SELECT SALES WITH S.CODE = "ABC"

n Records selected

EDELETE SALES

n Records deleted
Selects all records in the SALES file in which the S.CODE field contains ABC, and deletes them.

Continue Reading...

jbase Query Language COUNT

Reports the total number of records found in a file, which match the specified selection criteria.

Command Syntax

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

Syntax Elements

Options can be one or more of the following:

Options	Description
B	Suppress initial line-feed.
C{n}	Display 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. The n specifies a number other than 500 by which to increment. For Example, (C25) increments the counter after every 25 records processed.
P	Send the report to the printer.

Examples

COUNT SALES WITH VALUE > "1000"

91 Records counted

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

COUNT SALES WITH VALUE > "1000" (C50

91 Records selected 240 Records processed
91 Records counted

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

Continue Reading...

jBase Query Language BSELECT

Retrieves selected records and generates a list composed of data fields from those records as specified by any explicit or default output specifications. Each subvalue 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}

Comments : 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.
If you do not specify an output-specification, it uses the default data definitions "1", "2" etc.

Example

BSELECT SALES WITH S.CODE = "ABC]" P.CODE

Creates a list containing all P.CODE values from all the records in the SALES file, which have an S.CODE that starts with ABC

Continue Reading...

jQL Sentence General Rules

Entering a Sentence

The sentence is entered at the TCL prompt (:) or the select prompt (>) in an account which contains a pointer to the referenced file. If a verb such as SELECT or GET-LIST supplies an item, it displays the select prompt.

The following rules apply when entering a sentence:

1. The sentence is entered on one command line and is completed when selecting .
2. It must begin with a verb, defined in the MASTER DICTIONARY followed by a space.
3. It must contain a file name, which is defined within the account from, which the sentence is entered
4. Unless otherwise stated, one or more spaces should separate each element.

Line Continuation

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

1. Before the end of the 240 characters, enter CTRL+- and then press RETURN
2. Type the next part of the sentence. If a space is required at this point be sure to press the space bar either before or after step
3. If more than 240 characters are required repeat step
4. At the completion of the sentence press RETURN.

Example

If a sentence exceeds 240 characters (see below), the line continuation sequence (CTRL+- and RETURN) breaks the sentence into two parts.

LIST GUEST WITH INVOICE-CODE "15" AND WITH EACH INVOICE-CODE # "2" BREAK-ON LAST-NAME TOTAL INVOICE CTRL+- RETURN
GRAND-TOAL "AMOUNT DUE 'U' " DET-SUPP HDR-SUPP RETURN

Action by the System

Upon completion of the sentence, the system checks with the MASTER dictionary for a definition of the verb, which if defined, the system checks the file name and the syntax of the sentence. If you enter an invalid verb or file name or the syntax is incorrect, or have exceeded the limitations the system displays an error message.

Generating jQL Reports

You can display jQL reports at a terminal or send to a printer by using the LIST or SORT verb (and other verbs). The default output device receives the reports, which could be a printer (or other currently assigned spooler device) by adding LPTR or option p.

Columnar or Non-columnar format

If the report will fit within the page width of the output device, it is output as a series of columns. However, if the report would require more than the current page width to be output in columns, it is output as a series of lines (known as non-columnar format).

Continue Reading...

Entering jQL Command Sentence

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 whilst in jSHELL, it displays the select prompt. Each sentence must start with a jQL Command and can be of any length. Press to submit the constructed sentence. If you enter an invalid Command, the system will reject it and display an appropriate error message.

Example

SORT SALES WITH PART.NO = "ABC]" BY POSTCODE CUST.NAME
POSTCODE TOTAL VALUE DBL-SPC HDR-SUPP (P

SORT the jQL Command.
SALES the filename
WITH PART.NO = "ABC]"

The selection criterion: select all records, which contain a part number beginning with ABC.
BY POSTCODE
The sort criterion
CUST.NAME POSTCODE TOTAL VALUE

The output specification:

• Column 1 will contain the key of the SALES file
• Column 2 will contain the customer name
• Column 3 will contain the POSTCODE.
• Column 4 will contain VALUE (this is totaled at the end of the report)

DBL-SPC HDR-SUPP The format specifications - Double-space the lines and suppress the automatic header.

(P An option: redirect output to the system printer, rather than to the terminal.PART.NO, CUST.NAME, POSTCODE, VALUE.

References to data definition records defined in the dictionary level of the SALES file

Continue Reading...

jQL Command Syntax

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

Syntax Elements

JQL Command	One of the verbs like Commands detailed later. Most Commands will accept any or all of the optional clauses.
File specifier	Identifies the main data file to be processed. Usually the data section of a file, but could be a dictionary or a secondary data area.
Record list	Defines which records will be eligible for processing. Comprises an explicit list of record keys or record selection clauses. An explicit list comprises one or more record keys enclosed in single or double quotes. A selection clause uses value strings enclosed in single or double quotes and has at least one relational operator. If no record list is supplied, all records in the file will be eligible for processing unless an “implicit” record list is provided by preceding the Command with a selection Command such as GET-LIST or SELECT.
Selection criteria	Qualify the records to be processed. Comprises a selection connective (WITH or IF) followed by a field name. Field names can be followed by relational operators and value strings enclosed in double quotes. Logical Connectives AND/OR ca also be used. Expressions can be parenthesized to specify procedure.
Sort criteria	Specify the data list order. Comprises a sort modifier, such as BY or BY-DSND, followed by a field name. Used also to “explode” a report by sorting lines corresponding to multivalued fields by value, and to limit the output of values (see output specification).
USING file specifier	Defines an alternate file for use as the dictionary.
Output Specification	Comprises the names of the fields to be included in the report, optionally preceded by a BREAK-ONconnective or ‘TOTAL’ connective. Print limiters (Values strings enclosed in double quotes after the field name, optionally preceded by relational operators) can be used to restrict multivalue output.
Format specification	Comprise modifiers, such as HEADING, ID-SUPP, and DBL-SPC, which define the overall format of the report.
options	Comprises letters enclosed in parentheses, which modify the action of the Command to redirect output to a printer for Example.

Comments:A macro can substitute any element of a jQL Command sentence (with the exception of the Command and filename). When the REQUIRE-SELECT modifier is included in a jQL sentence it verifies that a select list is active before processing the sentence.

Macros

jQL allows the use of macros to predefine parts of a sentence. The macro definition contains one or more optional sentence elements. You invoke the macro by including its name in a sentence. The jQL processor looks for the macro in the currently active dictionary and includes all of its text parts in the sentence.

Using Clause

You may include multiple USING clauses in the sentence to specify that another file contains the attribute and the macro definition items referenced in the sentence. Each macro definition can also contain a USING clause to define the terms used within that macro.

Options

Options are letters enclosed within parentheses, which modify the action of the verb.

Print Limiters

Sort criteria and format specifications can include 'print limiters' which suppress output of particular values according to specified criteria.

Record IDs

jBASE treats record ID’s differently from other fields in a file, in that the record ID is the default field displayed by LIST/SORT commands and the implied field for selection clauses.

The @ID Field

The @ID Field is optional in jBASE files. The field name is @ID; the column name is set automatically to the name of the file, and the output format defaults to 10L.

The @ID Synonym

The @ID synonym is an optional entry you may find in some file dictionaries particularly in table dictionaries or in jBASE files converted from CONVERT.SQL utility. Under prime emulation, @ID contains the default output specification of @LPTR for output to printers.

Continue Reading...