Welcome to jBASE's new documentation site! Many answers to your questions can be found by searching the Knowledgebase or viewing the jBASE Documentation. We also have a Google Group for peer discussion about jBASE. If you are unable to find the information you are looking for, jBASE Support will be glad to assist in resolving your technical problems. Enjoy and please provide comments and feedback .

How can we help you?

JQL

Overview 

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 manner and to present the data in a flexible and easily understood format. The language is characterized by the use of intuitive commands that resemble everyday English language commands. For example, if you wanted to review a particular set of sales figures you might phrase your request like this:

"Show me the sales figures for January sorted in date order."

This request would translate into a jQL command 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.

jQL contains a rich range commands for listing, sorting, selecting and controlling the presentation of your data.

jQL 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 storage areas assigned, one for the data (the data section) and one 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.

Typically, all of the data fields in a file will be defined by data definition records kept in the dictionary portion of the 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 - but where you need to manipulate say dates (which are held in internal format), or to join data that is held in different files (perhaps even on remote systems), 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. They allow you to specify for example, 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 which will be used in the 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.

 

jQL Command Sentence Construction 

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

Optional clauses can be added 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. In general, JQL takes the form: 

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

 where: 

  • jQL-command is one of the verb-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.
  • sort-criteria specify the order in which data is to be listed. Comprises a sort modifier, such as BY or BY-DSND ( aka BY.DSND ), followed by a field name. Can also be used 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 to be used as the dictionary. There is no restriction of the number of USING clauses that can be included in a jQL sentence.
  • output-specification comprises the names of fields to be included in the report, optionally preceded by the TOTAL or BREAK-ON connective. Print limiters (values enclosed in double quotes after the field name, optionally preceded by relational operators) can be used to restrict multivalue output.
  • format-specification comprises modifiers, such as HEADING, ID-SUPP, and DBL-SPC, that define the overall format of the report.
  • options comprise letters enclosed in parentheses which modify the action of the command - to redirect output to a printer for example.

 

Note:

Any element of a jQL command sentence (with the exception of the command and filename) can be substituted with a macro.

When the REQUIRE-SELECT (or REQUIRE.SELECT) modifier is included in a jQL sentence, it ensures that a select-list must be active before processing the sentence.

When the REQUIRE-INDEX (or REQUIRE-INDEX) modifier is included in a jQL sentence, it ensures that a secondary index must be used for the selection.


Reserved words and symbols 

The following words and symbols have specific meanings when used in a jQL sentence. They should only be used as described later in this chapter and should not be used as user-defined dictionary names.

!
#
%
&
(
/
<=
<>
=
=<
*
+
-
>
><
A
AFTER
ALL
AN
AND
ARE
AS
ASSOC
ASSOC.WITH
ASSOCIATED
AVERAGE
AVG



BEFORE
BETWEEN
BREAK-ON / BREAK.ON
BREAK-SUP
BSELECT
BY-DSND / BY.DSND
BY-EXP / BY.EXP
BY-EXP-DSND / BY.EXP.DSND


CALC
CALCULATE
CAPTION
CNV
COL-FILLER
COL-SPACES
COL-SUPP
COL.HDG
COL.HDR
COL.SPCS
COUNT
COUNT-SUPP
COUNT.SUP
COUNT.SUPP

DATA
DBL-SPACE
DBL-SPC / DBL.SPC
DET-SUPP
DICT
DISPLAY.NAME
DISPLAYLIKE
DISPLAYNAME


EACH
EDELETE
ENUM
ENUMERATE
EQ
ESEARCH
EVAL
EVERY


FILE
FIRST
FMT
FOOTER
FOOTING
FROM




GE
GRAND-TOTAL
GT
HDR-SUPP
HEADER
HEADING
I-DUMP
ID-SUPP
ID.ONLY
ID.SUP
IF
INQUIRING
ITEMS



LE
LIKELIST
LIST-INDEX
LIST-LABEL
LPTR




MARGIN
MATCH
MATCHES
MATCHING
MAX
MULTI.VALUEMULTIVALUE




NE
NI-SUPP
NI.SUPP
NO
NO-INDEX
NOPAGE
NO.PAGE
NO.SPLIT
NOT
NOT.MATCHING
OF
ONLY
OR
PAGE
PCT
PERCENT
PERCENTAGE
PG
REFORMAT
REQUIRE-INDEX
REQUIRE-SELECT
RETRIEVE
 
S-DUMP
SAID
SAMPLE
SAMPLED
SAMPLING
SAVING



SELECT-ONLY
SINGLE.VALUES
 SINGLE.VALUED
SORT
SORT-ITEM
SPOKEN
SREFORMAT
SSELECT
ST-DUMP
STATSUBVALUE
SUPP
said
spoken
~

T-DUMP
T-LOAD
TAPE
THE
TO
TRANSPORT




UNIQUE
UNLIKE
USING
VERT
VERTICALLY
WHEN
WITH
WITHEACH
WITHIN
WITHOUT


In most cases, a '-' in a compound word can be substituted for a '.' (e.g. LIST-LABEL is synonymous with LIST.LABEL), and those synonyms are also reserved. However, words in the table with a '.' have no equivalent word with a '-' (e.g. there is no ASSOC-WITH word).


Entering a JQL Command Sentence 

A jQL command sentence is entered at the shell in response to a command prompt (:) or a select prompt (>). The select prompt is displayed if an implicit record list has been created by a command such as SELECT or GET-LIST whilst in jSHELL. Each sentence must start with a jQL command and can be of any length. Having constructed your sentence, you submit it for processing by pressing the RETURN key.

If an invalid command is entered,  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 SAMPLE 100 (P

where:

SORT
is the jQL command.
SALES
is the filename.
WITH PART.NO = "ABC]"
is the selection criterion. Select all records which contain a part number that starts with ABC.
BY POSTCODE
is the sort criterion.
CUST.NAME POSTCODE TOTAL VALUE
is the output specification. Column 1 will contain the key of the SALES file, column 2 will contain the customer name and column 3 will contain the POSTCODE. Column 4 will contain VALUE and will be totaled at the end of the report.
DBL-SPC HDR-SUPP
are the format specifications. Double-space the lines and suppress the automatic header.
SAMPLE 100
take the first 100 records as a sample
(P
is an option. Redirect output to the system printer, rather than to the terminal.
PART.NO, CUST.NAME, POSTCODE, VALUE
are references to data definition records which are defined in the dictionary level of the SALES file.

 

Default Data Definition Records 

When you issue a jQL command that does not contain specific references to data definition records, and you do not suppress output of the report detail, the system will attempt to locate any default data definition records that you may have 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 that does 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 the data definition is not found in the dictionary (or the file specified in a USING clause) the system look in the files specified in the JBCDEFDICTS environment variable but only if it as been assigned.  If JBCDEFDICTS has not been assigned, 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 files specified in the JBCDEFDICTS environment variable (if JBCDEFDICTS is assigned) and then in the file specified by the JEDIFILENAME_MD environment variable (if JBCDEFDICTS is not assigned).

In this way, you can set up data-specific, file-specific or account-specific defaults to be used with any jQL command.

 

jQL Output (Reports) 

By default, output from a jQL command will be displayed on your terminal, in columnar format, with a pause at the end of each page (screenful).

 

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 report is displayed at the terminal and extends over more than one screen, press RETURN to view the next screen. To exit the report without displaying any remaining screens, press <Control X> or "q".

 

BREAK-ON OPTIONS (Summary)

B - The break value appears in the heading; requires the use of 'B' in the HEADING
D - Suppresses the break data line if only one control break
L  -  Suppresses the blank line at a control break
N - Resets the page number
P  - Ejects a page on a control break
U - Underlines TOTAL fields
V  - Inserts the Value at the control break

 

OPTIONS (Summary)

C  Display running Counters. See SSELECT for details.
N  Suppress paging (i.e. Nopage)
P  Send output to the spooler
R  Suppress 'Error 202 Record not on file' errors


Back to jBASE Query Language (JQL)


Was this article helpful?