# jBASE Query Language (jQL)
Read Time: 8 minute(s)
# Understanding jQL
jQL is the query language used by jBASE. It takes the form of dictionary items used for selecting and sorting rows, and for displaying columns. The output, by default, uses sequential ID's in the dictionary of:
- The specified file or
- Universe, Unidata and Prime emulations uses the @ phrase
This query language is capable of calculating column totals, averages, and percentages with the appropriate modifiers applied. Further, modifiers, if correctly applied, can alter the way a dictionary is used.
# jQL or SQL
Some differences exist between jQL and SQL. They can be summarized as follows:
|Dictionary calculations||Command line calculations|
|Many dictionaries per field||One dictionary per field|
|End-user tool||Developer tool|
|Simple syntax||Difficult syntax|
# Parts of a jQL Sentence
There are 5 parts to a jQL sentence.
- Command – the action to be taken.
- File name – the data file upon which the action is taken.
- Selection criteria – reduces the number of records in the query.
- Sort criteria – puts resulting records in user defined order.
- Output specifications – fields to display and additional formatting instructions.
# Finding A File
The MD is searched first if
JEDIFILENAME_MD is set, then
JEDIFILEPATH is searched.
# Selection Criteria
WITHOUTmodifies the dictionary to be used as selection criteria.
- Relational operators are used to compare query value to data base contents. The following may be used:
ORare used when multiple selection criteria are required.
may be used for sub-string extraction. The
LIKEcan also be used.
Some examples of selection criteria are as follows:
LIST CUSTOMER WITH CUS.NAME = “A]” CUS.NAME
LIST CUSTOMER WITH CUS.NAME LIKE “A…” CUS.NAME SORT ORDER WITH ORD.DATE GE “10/01/96” AND LT “11/01/96” ORD.DATE
# Sort Criteria
Different key terms will sort search results differently as can be noted bellow:
BY.EXP- explode a multivalue field, ascending
BY.EXP.DSND- explode a multivalue field, descending
Exploded sorts treat each multivalue as a separate column (field).
It is possible to have multiple sort levels, with each subsequent level a sub sort of the previous level. With regards sort justification, it is based on the dictionary (yes, you can sort numbers left justified).
Examples of Sort Criteria are as follows:
SORT ORDER BY ORD.DATE ORD.DATE
SORT ORDER BY-DSND ORD.DATE ORD.DATE
SORT ORDER BY ORD.DATE WITH ORD.DATE GE “10/01/96” AND LT 11/01/96” ORD.DATE
SORT CUSTOMER BY-EXP ORDERS ORDERS ORD.AMT ORD.DATE CUS.NAME
# Output Specifications
Any field name without a modifier is displayed
Examples of Output Specifications are as follows:
SORT ORDER BY CUS.NAME BY ORD.DATE WITH ORD.DATE GE “10/01/96” AND LT “11/01/96” AND WITH ORD.AMT GE “500” BREAK-ON CUS.NAME “Total For ‘VUD’” ORD.DATE TOTAL ORD.AMT AVERAGE ORD.AMT HEADING “October 1996 Orders Over $500 By Customer’CL’”
SORT ORDER BY CUS.NAME WITH ORD.AMT GE “500” BREAK-ON CUS.NAME TOTAL ORD.AMT AVERAGE ORD.AMT DET-SUPP ID-SUPP
# Types of Dictionaries
Type A or S: Legacy style 10 line dictionary. Data descriptor or calculated. From the days when the data base was the OS.
Type D: Legacy style 5 line data descriptor. Actually it’s 7 lines, but jBASE may ignore two of them, and therefore may not be as functional as the legacy in some cases.
- This type was popular when the database was implemented on another OS.
Type I: Legacy style 5 line calculated column. Actually it’s 7 lines, but jBASE may ignore two two of them, and therefore may not be as functional as the legacy in some cases.
- This type was popular when the database was implemented on another OS.
Type M or PH: macro or phrase, 2 lines.
Macros and Phrases:
Are file dictionary items that contain frequently used portions of a jQL sentence.
001+ M or PH (macro or phrase) 002+ jQL sentence parts or comments
For example, if a macro named MYMACRO has been created containing M and CUS.NAME CUS.ADDR CUS.STATE on line 1 and 2 respectively, executing the
jED DICT CUSTOMER MYMACRO command will bring in the editor something that resembles the text below:
001+ M 002+ CUS.NAME CUS.ADDR CUS.STATE
Thus issuing a query such as:
LIST CUSTOMER MYMACRO will automatically include the above listed portions of JQL.
# Dictionaries again
- I Types
001 I 002 Formula 003 Conversion code 004 Column heading 005 Format
- D Types
001 D 002 Attribute # 003 Conversion code 004 Column heading 005 Format
- 10 line structure
001 A 002 Attribute # 003 Column heading 004 Controlling/Dependent attributes 005-006 Blank 007 Conversion codes 008 Correlative codes 009 Justification 010 Column width
# Dictionary Processing
Most Common Conversion Codes
These go on lines 3 (of an I or D type), or 7 and 8 (of an A type); and are also used with the
FMT jbc functions
MDor MR – decimal
G– group extraction
T– text extraction
Tfile– table lookup
- There are more!
# Date Conversions
With jBASE, the internal value is the number of days from December 31, 1967. So if the internal value for date happens to be 10000,
D- 18 MAY 1995
# Decimal Conversions
If for instance the Internal value for a value is 100000,
MR conversion has additional formatting capabilities.
# Time Conversions
With jBASE, the internal value is the number of seconds from midnight. So if for instance the internal value is 55555
# Group Extraction
This feature allows for the extraction of data based on a delimiter. If for instance the Internal format is 123*456*789, then it will follow that:
On line 2 of an I type
FIELD(CUS.NAME,’ ‘,1,2)- returns the first two words of the field CUS.NAME
# Text Extraction
Allows for extracting data based on character position. If for instance an internal value of '123456789'
T1- 1 if left justified, 9 if right justified
Use in a query on line 2 of an I type:
# Table Lookup Example
- Country code stored in field 12 of a CUSTOMER table
- Country names stored in field 1 of the COUNTRY table with country code as the primary key
- In the dictionary of the CUSTOMER table
- Field 2 references the field containing the lookup date (12)
- On line 2 of an I type
# I Types
Field 2 contains the formula which can be made up of:
- Dictionary names
- @Variables -
- jBC functions –
- User subroutines – first argument is the return value
- jBASE supplies subroutines for multivalues
- Multiple expressions separated by a semi colon ( ; )
Theses are user developed subroutines which perform user defined conversions. They take 5 arguments, that is :
- result - return value.
- source - original value.
- code - actual conversion code.
- type - 0 =
ICONV, 1 =
OCONV, -1 =
FMTis used for dictionary conversions (line 7 or 3), the
CRT variable format.
- error - non-zero enters debugger.
A standard conversion subroutine code cannot be used for custom subroutine code.
# Custom Date Conversion
SUBROUTINE JBCUserConversions(result, source, code, type, error) error = 0 BEGIN CASE CASE code = “JCAL” * Day of Week: Month Day, Year DOW = OCONV(source,”DWA”) MONTH = OCONV(source,”DMA”) DAY = OCONV(source,”DD”) YEAR = OCONV(source,”DY”) result = type:” “:DOW:”: “:MONTH:” “:DAY:”, “:YEAR CASE 1 result = “” END CASE RETURN END
# Using JBCUserConversions
jed DICT ORDER MYDATE 001 A 002 3 003 My Date 007 JCAL 009 L 010 35 LIST ORDER ORD.DATE MYDATE
jed DICT ORDER MYDATE 001 D 002 3 004 My Date 005 35L LIST ORDER ORD.DATE MYDATE
jed DICT ORDER MYDATE 001 I 002 OCONV(ORD.DATE,”JCAL”) 003 004 My Date 005 35L LIST ORDER ORD.DATE MYDATE
Back to Getting Started