 
|
|
jQL OverviewThe 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 CONSTRUCTIONA 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.
COMMAND SYNTAXjQL-command file-specifier {record-list} {selection-criteria} {sort-criteria} {USING file-specifier} {output-specification} {format-specification} {(options}
SYNTAX ELEMENTSjQL-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, 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.
NOTESAny element of a jQL command sentence (with the exception of the command and filename) can be substituted with a macro - see later. 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 SYMBOLSThe 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.
Note: 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 SENTENCEA 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 you enter an invalid command, the system will reject it and display an appropriate error message.
EXAMPLE
where:
DEFAULT DATA DEFINITION RECORDSWhen 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 DEVICEYou can redirect the output to a printer (or the currently-assigned Spooler device) by using the LPTR format specifier or the P option.
REPORT LAYOUTIf 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.
PAGINGIf 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
OPTIONS (Summary)C Display running Counters. See SSELECT
for details. jQL | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||