Query Instructions The Query program will allow you to produce a report based on your selection, sorting and formatting specifications. Upon entry, you must use the Menu to open both a query command file and a database. You may use the editor to build a customized query command file or you may load a stored query command file. Query command files are constructed by entering Select, Sort and Report commands. Upon opening a database, you will be presented with a data dictionary. The data dictionary gives a list of data names that can be used to display or print values contained in the database. The SELECT command allows you to specify some selection criteria to be used to determine which database entries will be included in you output display or report. For example, select (payee = "Allstate" or "State Farm") and amount > 100.00" specifies that the database is to be searched for Payees with the name of "Allstate" or "State Farm" with an Amount > $100.00. Note that the Select parameters contain parentheses to prevent ambiguities. If there is a possibility that the key word or phrase you are looking for contains occurrences with both upper and lower case letters, you must check for both, e.g., "Allstate" or "allstate". Another method is to use the wild character, e.g., "?llstate". The SORT command allows you to specify the sequence in which the selected records are to be displayed or printed. For example, sort: date, payee specifies that the selected database entries are to be sorted in Payee sequence within Date. A maximum of five Sort fields may be specified. Dates are automatically placed in the correct sequence according to the year, e.g., "12/31/87" will be sorted before "01/01/88". The REPORT command allows you to specify the format of the output display or report. For example, report date, col 13, type, col 18, payee, col 44, amount specifies that the Date, Type, Payee and Amount for the selected records are to be displayed. Further, it is specified that the Date is to be displayed beginning in column 1, the Type in column 13, the Payee in column 18 and Amount in column 44. The Report specification parameters may be very simple or very complex depending upon the skill of the user. The Report specification parameters can be classified as either describing "when to do it" or "what to do". When To Do It: page_top - specifies that subsequent functions are to be executed at the top each printed page or screen. page_bottom - specifies that subsequent functions are to the executed at the bottom of each printed page or screen. report_top - specifies that subsequent functions are to be executed only at the beginning of the output report. report_bottom - specifies that subsequent functions are to executed only at the end of the report. top() - specifies that subsequent functions are to be executed after there has been a change in the content of . For example, top(Payee). Normally, the specified data-name will be the same as the sort sequence. bottom() - specifies that subsequent functions are to be executed before there is a change in the content of data-name. For example, bottom(EC). detail - specifies that the subsequent functions are to be executed for each selected database entry. What To Do: print - specifies that all output is to be routed to the printer. Default values for tm, bm and bod are set to 4, 8, and 54 respectively. display - specifies that all output is to be routed to the screen. "Display" is the output default. Default values for tm, bm and bod are set to 0, 0, and 22 respectively. tm nn - sets the top margin for the output display or report to the value specified by nn. bm nn - sets the bottom margin for the output display or report to the value specified by nn. bod nn - sets the body of the output display or report to the value specified by nn. The sum of tm, bm and bod must equal the desired page length. - causes the specified data name to be printed or displayed. For example, "Payee" is a data-name. "literal" - causes the specified literal to be printed or displayed. For example, "The result is:" is a literal. trim() - caused the specified data name to be printed or displayed after all trailing spaces have been eliminated. For example, "trim(Payee)". total() - specifies that the summed value of is to be printed or displayed. An numeric data item from the dictionary or a numeric literal must be substituted for . For example, "total(amount)". "total(1)" can be used to provide a count of the transactions. cum() - same as "total" except that the accumulator is not reset after displaying the total. This function is used to produce a running balance. spacer nn - causes nn spaces to be inserted between output data items. The default is 1. col nn - causes the next specified data item to be displayed or printed in the column specified by nn. skip n - causes n lines to be skipped before displaying or printing the next data item. If n is not provided, 1 is assumed. pause - causes a pause in the output process until a key (any key) is depressed. eject - if "print" has been specified, this command causes the paper to be ejected to the top of the next page; otherwise, the screen will be cleared. EDITING MASKS Numeric Editing Masks may be used to cause predefined characters to be imbedded within displayed or printed numeric items. Numeric items are identified in the data dictionary with the character "n" following the data name. The masks must be constructed as an alphanumeric literal whose first character is "%". If the second character of the mask literal is other than a numeric character, that character will be placed in the next left most position before the resulting edited numeric value. Examples: Input Values Mask Output 01000 "%9,999" 1,000 25245 "%$99,999" $25,245 -5643 "%$999,999" $-5,643 Alphanumeric Editing Masks may be used to cause predefined characters to be imbedded within displayed or printed alphanumeric or date items. Alphanumeric items are identified in the data dictionary with the character "c" following the data name. Date items are identified in the dictionary with the character "d" following the data name. The mask must be constructed as an alphanumeric literal whose first character is "%". Examples: Input Values Mask Output 123456789 "%@@@-@@-@@@@" 123-45-6789 102786 "%@@/@@/@@" 10/27/86 Here is an example of a more complex query construction: select (ec = "UE" or "UW" or "UP") and date = "????88" and type = "C" sort ec report print date "%@@/@@/@@" payee ec amount "%9,999,999.99" bottom(ec) total(amount) "%9,999,999.99" This query selects transactions with Expense Codes of "UE" or "UW" or "UP" that have a Date ending with "88" that also have a Type of "C". Note that the question mark (?) is used as a wild character, i.e., it is used to denote "any character". The output report is to be printed and will contain the Date, Payee, Expense Code and Amount. A total will be printed when there has been a change in the expense code. Several query command file examples have been included on your Bank'n diskette. Use these query command files, or modify them to fit your needs. Rules: (1) The Select statement may contain these operators: = Equal to <> Unequal to < Less than > Greater than <= Less than or equal to >= Greater than or equal to (2) The field on the left side of the operator must be a data name from the dictionary. (3) The field on the right side of the operator may be either a data name or literal. Literals must be enclosed in double quotes. (4) In the case of equal and unequal, the data name on the left side of the operator is searched for any occurrences of the data specified on the right of the operator. (5) The length of the item to the right of the operator may not be greater than the length of the item to the left of the operator. (6) When used in a literal, the "?" is wild. For example, to select all records in the database enter: select date = "?" (7) Commas may used to improve readability; otherwise, they serve no useful purpose. (8) All data names and commands must be in lower case. (9) When a vertical bar "|" is encountered in the query command file, the remainder of the line is assumed to be a comment. If your printer carriage is 80 columns in width, try setting your printer preferences for compressed mode in order to print reports that are greater than 80 characters in width. Also, if your printed output does not have the expected appearance, check your printer preferences.