 |
|
|Main
| Previous
Page |
|
|
Query Builder At-a-Glance
also see QueryBuilder
Open Save OK Cancel Retrieve a saved query Save and store a query Run a query Exit Query Builder with “.wis” extension and return to menu
Verb: Take action with verb such as LIST, SORT, SELECT, SSELECT. Four verbs are displayed on the screen, but others can be activated by simply typing the verb in the box, e.g. LISTDICT, DSPLY, etc.
File: Enter file name from which records are selected, and information is retrieved for reporting, e.g. PERSON.ST, STUDENTS, STUDENT.PROGRAMS, STUDENT.TERMS, etc.
Items: Formulate criteria by choosing the data field/I-descriptor, selecting operator and setting values.
Sort: Sequence records using BY (ascending order) or BY.DSND (descending order).
Output: Set columns of information on report. The first column of data displayed by the system is usually the record IDs of the file, unless you tell the system to suppress them. The width and name of a column may be reformatted to fit your report, using modifiers such as FMT, COL.HDG.
Heading: Description for report heading. If you do not set your own report heading, the system displays the LIST/SORT query that you have developed as the default header on top of the page.
Footing: Description for report footing. The footing works the same as heading, but is displayed on the bottom of the page.
Grand Total: (This tool is not supported on our system.)
Output to: Options for display location Suppress: Options to “turn off” certain display of info
˛ Screen " Detail lines ™ Report Viewer " Item ID ™ Host Printer " Page heading ™ Local Printer " Col. Heading ™ PC/Process
Before: System process to be performed prior to running the query in the main body. For example, using GET.LIST to retrieve a savedlist of record IDs before running a LIST query for generating report.
After: System process to be performed after running the query in the main body. For example, using SAVE.LIST to store the record IDs after running a SSELECT query for retrieving records.Query Builder
Introduction
After logging on to Datatel system, a screen similar to the one below appears on your PC.
To start Query Builder, click on Options on the Menu Bar and then click on Query Builder. System will bring up Datatel Text Window, followed by the screen for Query Builder.
Introduction (cont’d)
In Query Builder, commands and attributes must be typed in capital letters. A string of literal, i.e. text in your own words, may be in upper and lower cases. Use the mouse or Tab key to navigate from one box to the next. Using the Enter key will take you to the processing screen displaying an error message.
To exit the display of data, type “Q” for quit and press Enter key twice. Please note that even after typing, the letter “Q” does not appear on the screen. You still proceed with pressing Enter key twice.
Here are some general descriptions on the four buttons at the top of the Query Builder screen.
Open It opens a saved query. This makes it very convenient for an end-user to run a job that uses the same query routinely with little or no modification to the language.
Save It saves a query. You may save queries on to a diskette (A:\) or into a folder in your hard drive (C:\), e.g. C:\QUERY\PRSNZIP.WIS. Please note: do not save queries on the host system.
When naming a query, add extension “.WIS” to the name. For procedural documentation, it’s a good practice to keep a listing of the names of the queries and a brief description on each of them.
OK OK button causes the system to run the query. If you press this by mistake, the system might return with an error message on the screen.
Cancel It allows you to exit Query Builder and takes you back to the Datatel Text Window.
Additional descriptions for Clear and Help buttons that are on the lower right hand side.
Clear Click this button to erase the query sentence displayed on screen. If you wish to save your query, be sure to click on Save button before erasing.
Help It brings up wIntegrate Help screen for looking up tips on Query Builder. Verb
LIST Retrieves records from a file and displays data in the order the records are stored. Data are “listed” either on screen or in a report sent to the printer. You do not use this verb in conjunction with SAVE.LIST to do a savedlist.
SORT Same as LIST, with records arranged in a sequential order according to record IDs (@ID) by default, unless you tell the system otherwise. You do not use this verb in conjunction with SAVE.LIST to do a savedlist.
SELECT Creates an active list of record IDs that you can use with other commands or user-defined process. It neither displays data nor generates report. You need to use this verb when you wish to use SAVE.LIST to do a savedlist.
SSELECT Same as SELECT, with records arranged in a sequential order according to record IDs (@ID) by default, unless you tell the system otherwise. You need to use this verb when you wish to use SAVE.LIST to do a savedlist.
Other verbs: DSPLY To display data fields of a record according to its formatted dictionary elements LISTDICT To print the dictionary of a file by Type, then by Location LIST DICT To print the dictionary of a file by Type, then by field name. File
Enter the file name, if known. Or you can click on the File button for a listing of file names.
When you click on the File button, Query Builder displays the files available in the account. It takes the system a few seconds to build the list, so allow enough time for a complete listing.
Use the vertical scroll bar to locate the name of file you need. Highlight the name and click on OK button to make your selection.
File (cont’d)
To view the dictionary at this point, highlight the name of the file you need. Click on the box labeled “View Dict” to bring up an alphabetical listing of names for data fields and I-descriptors.
Make use of both the vertical and horizontal scroll bars to view the dictionary list. To copy the columns to Excel, select and highlight as many columns as you need by clicking on the column heading. Click on Copy to Excel under the menu item Edit. The system will perform a transfer to an Excel spreadsheet.
To exit this screen, click on File and then Exit. System takes you back to the list of files.
Items
You may formulate the criteria in your query by typing in the commands and phrases in the Items box. For example, WITH ZIP BETWEEN “52801” “52809” when you are searching for records with those zip values.
You may also open the Items dialog box by clicking on the Items button for a display of fields, operators, and options to help you develop a query.
On the left of the screen is an alphabetical listing of data fields and I-descriptors. Highlight the name where you can find the data value for setting your criterion. The selector, WITH – which is essential in a query – is automatically inserted into the query by the system when you choose a data field.
In the middle is the display of Operators that you need when setting your criteria in the query. Choose an operator by highlighting the symbol.
= Equals to < Less than > Greater than <= Less than or equal to >= Greater than or equal to # Not equal to LIKE Similar data, with the use of ellipsis (…) BETWEEN Define a range of values with the end values included (This operator is not available within the Items dialog box.)
In the box labeled “Value”, you enter the data that can be found in the highlighted data field/I-descriptor. For example, 52803 is a value for the field ZIP; 06/30/75 is a value for the field BIRTH.DATE. Items (cont’d)
Depending on the logic of the criteria, you may attach phrases in your query by checking the box labeled “And”. When this is not checked, the phrases are defaulted with the conjunction “Or”.
Click on Add button, as you are ready to build your query. Insert button inputs a new criterion to the line above where you have highlighted. Replace button inputs a new criterion in place of the line where you have highlighted. Delete button erases the line you have highlighted.
Sort
Records can be arranged in certain order according to the sequence you set within the query. You may include more than one sequential criterion. This command allows you to retrieve the records in the sequential order using the BY or BY.DSND clause followed by the name of an attribute to sort on. If you have no need for records to be sorted in any particular way, the system defaults to sorting records by the @ID field in ascending order.
Highlight the name of data field/I-descriptor and then click on one of two Add buttons, depending on whether you wish the sequence of records to be in ascending or descending order.
The example below shows two levels of sequencing. First, the selected records will be sorted according to the current status of the application. Second, the records will be sorted according to the name of applicant within each group of application status.
Output
Whether you wish to display information on the screen or to generate a report via the host printer, you need to name the data fields/I-descriptors where data are being stored. The four buttons – Add, Insert, Replace, Delete – they all function the same way as described under Items.
Output (cont’d)
There are several output options available for your use: BREAK.ON, COL.HDG, DBL.SPC, FMT, TOTAL.
BREAK.ON To separate records that have been sorted and grouped by an attribute, and skip a line before the next grouping of records, e.g. BREAK.ON ZIP. It works best when records have been sorted in a sequential order using the BY or BY.DSND clause, breaking on the field used in the first sort level.
COL.HDG To change the existing column heading and to include within a pair of double quotes the heading you wish to display, e.g. BIRTH.DATE COL.HDG “Date of Birth”.
DBL.SPC To allow double spacing between each record or each grouping of records when you include this command at the end of your Output statement.
FMT The width of a column may be reformatted, followed by the desired length of characters with left/right justification specified within a pair of double quotes, e.g. LAST.NAME FMT “15L”. Numeric fields used for calculations should always have right justification, e.g. VEN.YTD.VOU.AMT FMT “10R”.
In case when the column heading is wider that the reformatted column width, you need to change the column heading using the option COL.HDG.
TOTAL It gives the column total of a numeric data field, e.g. TOTAL VEN.YTD.VOU.AMT.
Heading
By system default, the LIST or SORT query you use is the heading of the report. You may do a different report heading by setting the wording in the box labeled “Definition.”
Other system commands can be defined in this area as well by clicking on the buttons. Listed below are some common ones used, with a brief description and prompt display for each.
Date Date of report generation ‘D’ Time Both time and date of report generation ‘T’ Page No. Page number of report ‘P’ New Line A blank line ‘L’ Center Center the wording in the heading ‘C’ File Name File name printed on report ‘F’
Here is an example of a Heading with ‘D’ prompting for a date, followed by a report heading in your own words, the word “Page”, ‘PL’ prompting for page number and a blank line between the heading and the columns in the report.
Footing
Footing works the same as Heading with the same set of system commands, except the text shows up on the bottom of each page of the report.
Here is an example of Footing at the bottom of the report with ‘LF’ prompting for a blank line between the columns and file name.
Grand Total
At present, the Grand Total
function is not supported by this system.
Your query may look similar to the one shown below.
When using the verb LIST or SORT, you have the options to use the following boxes labeled: Verb, File, Items, Sort, Output, Heading and Footing. These provide functions for data display on the screen or report generation via your printer.
Query Builder screen (cont’d)
When using the verb SSELECT or SELECT, you have the options to use the following boxes labeled: Verb, File, Items and Sort. Since neither SSELECT nor SELECT allows you the display of data, remember not to do any entry in Output, Heading, Footing or Grand Total. Otherwise, an error message will appear on the screen when you try to run the query.
This example shows a correct SSELECT and SAVE.LIST process.
Query Builder screen (cont’d)
Since SSELECT or SELECT query is not a statement you can use for display of data, either on the screen or on a report, you should not use the boxes labeled: Output, Heading, Footing or Grand Total. If you do, an error message will appear on the screen
This example shows an incorrect process.
Query Builder screen (cont’d)
The circumflex symbol at the end of the dotted line in the error message tells you the proximity where an error may be found. The system does not tell you exactly what is wrong with the query.
In the example below, verb SSELECT is used in conjunction with listing data from field X.PST.FULL.NAME in Output. You either must drop what is in Output because SSELECT does not allow display of data, or change the verb to LIST for data display.
Before
When you set command in the box labeled “Before”, you are initiating a process that the system needs to run prior to performing the task stated in the query in the main body.
Using the example below, you are retrieving an existing savedlist of record IDs from PERSON.ST file before running another query, LIST. The command for retrieving a savedlist is GET.LIST, followed by the name of the savedlist, GET.LIST X.LLB.PST.MPT. The system will perform the retrieval as stated in “Before”, then finish the second task as stated in the main body, the LIST query.
To learn about saving a list of records, please refer to the documentation on After.
|
