"SBase Personal - 14. DEFINING AND USING QUERIES" 14 DEFINING AND USING QUERIES Query Applications Opening a Query The Query Definition Requester Defining the Query Title, Date, and Page Number Query Command Panel Selecting Query Output Destination Defining Query Output Selecting File and Fields Defining Derived Columns Defining Column Headings Defining Column Position Defining Field Length for Output Defining Numeric Field Format for Output Inserting Lines Page Oriented Output Printing Long Text Fields Additional Format Parameters Defining Report Features Defining a Query Filter Defining a Sorting Order Setting the Number of Characters on which to Sort Single and Multiple File Sorting Sorting on a Multi-field Index Running a Query Saving a Query 14 DEFINING AND USING QUERIES The Query command is a powerful and versatile tool for selecting data. In this chapter we explain how to define single file queries, focusing on basic concepts and operations. See also Chapter 15 Reporting and Chapter 17 Multi-file Applications. The DML language of SBase Professional 4 offers a more powerful query facility in the form of a multi-line SELECT statement. This allows you to construct queries with more output fields, search conditions, ans sorting levels than are possible in the Query Definition requester. The SELECT statement may also be combined with a set of Repoting commands to produce fully formatted and analyzed reports. The Form Designer Pro's Report Generator creates report programs that make use of both SELECT and the Reporting commands. For many users, though, the Process Query menu command provides all the functionality they require. If you use this command to build up a library of re-usable queires on disk, you can run them later from within a DML application created by SBase Professional 4. Query Applications Query is the most sophisticated of SBase 4's menu commands. In some ways it is similar to the Filter facility available from the browsing controls and in Project Print. But unlike the Filter, Query allows you to define not just one, but four command lines: Fields, Report, Filter and Order lines. Each line is used to specify a different aspect of the query output. It allows you to produce formatted output with a great degree of control over both the selection and the presentation of record data. Query is a multi-purpose command which has five main areas of application. 1. Creating complex filters. In many applications, you may find that you need to retrieve the same set of records over and over again; you may need to check groups of records on a regular basis using the same or similar selection criteria. If you used the browsing controls filter you would have to enter the filter conditions in every session. One of the advantages of using Query is that you can save a query on disk. 2. Multi-file applications. By setting up a relational link in the Query Filter, you can select data from several files at the same time. See Chapter 17 Multi-file Applications. 3. Reporting. The report features available in Query provide additional information about the results of a search, such as record counts or field totals. See Chapter 15 Reporting. 4. Sorting. Query output can be sorted into any order. Indexing already provides one way of sorting a file, but with the Query facility you can sort records in either ascending or descending order. You can also specify several levels of sorting, using a different field for each level. 5. Output redirection. The output from a query can be directed to one of four possible destinations: the screen, the printer, a text file on disk, or a new database file. This last output command lets you create a new database file which combines the data from a number of existing files. Opening a Query SBase 4 queries are re-usable. They are stored on disk as SBQ files, which contain the information you define for the four parts of the query, plus the names of all files involved in the query. Even if the query you want has not been defined and stored, a similar one may exist, and it is probably easier to read that one and change it than to start from scratch. To open an existing query: 1. Select the Query Open command from the Process menu. 2. Select the required query from the list. - If the left-hand panel is empty, there are no queries in the current directory. 3. Click OK. - Any file involved in the query that is not already open is opened automatically at this point. SBase opens the query and presents you with the Query Definition requester. If you want to run the query immediately, just click OK or press ENTER. See also Saving a Query at the end of this chapter. Once you have defined a query, that query definition remains available until you define another one or end the SBase session. To rerun your last query, or to change it before rerunning it, or to define a new query: 1. Select the Query Edit command from the Process menu. - The last-used query definition is displayed in the Query Definition requester, or an empty Query Definition requester is displayed. 2. Create a new definition, or edit the displayed one, as required. To remove a definition from the requester in order to create a completely different one, click on Clear. 3. When you are satisfied with the query definition, click OK to run it. See also Saving a Query at the end of this chapter. The Query Definition Requester The requester has three parts (see below). At the top is the Title Definition, below this is the Query Command Panel, and at the bottom left you will see four buttons for selecting the destination for the output from the query. Query Definition works by accepting your selections and typed input through four requesters linked to the Fields, Report, Filter and Order buttons next to the Query Command Panel. SBase 4 then searches the file or files and outputs any records that match your Filter values. *** See Fig 14-1 *** Defining the Query Title, Date, and Page Number Title Definition has three parts: Date check box, Text box, and Page Number check box. If you make any selections or enter any text, SBase 4 will print the results as a Title at the head of each page or screen of the query output, followed by a blank line. If you don't define a title at all, SBase 4 starts the query output at the top of the page. If you click the Date check box, the Title will include the system date, as shown in the Date Format requester on the Set menu. The date is always aligned with the left margin. To enter the text you want for the query output title, click in the box between the Date and Page Number check boxes, and start typing. The maximum length is 80 characters. If you click the Page Number check box, the Title will include a page number. The page number starts at one and increases by one every time it is printed. The page number is always aligned with the right margin. Field names are output as the default column headings. You can redefine column headings with your own alternatives, or suppress them entirely. See `Field' parameter, later in this chapter. Query Command Panel The four lines of the Query Command Panel do most of the work of the Query command. Each line defines one of the elements of a query: output fields, report features, filter conditions and sorting order; each of the first three can be up to 512 characters long. - Fields determines which fields are output and allows you to format the output. Query output is normally tabular, but you can specify a page orientation if you prefer. See Defining Query Output, later in this chapter. - Report allows you to include other features such as totals and record counts. It also sets subtotal breaks. See Chapter 15 Reporting. - Filter controls the selection of records from a file. As a multi-file filter, it allows you to set up relational links between files. By doing this, you can make the selection of records in one file dependent on records in another file. See Defining a Query Filter, later in this chapter. - Order specifies the order in which records are sorted. You can sort a file on one field - this sorts records in the same way as an index - or you can use several fields to sort a file at different levels. See Defining a Sorting Order, later in this chapter. You do not have to use all four lines. For example, to produce a sorted report that prints just the selected fields for all the records in the current file, you would only use the Order line to specify the fields to sort on. Selecting Query Output Destination You select the destination for the query output by clicking on the appropriate radio button on the Query Definition Requester. Then you run the query by clicking OK. Screen Directs output to the Screen. Printer Directs output to the Printer. Disk This selection creates a text file on disk containing the query output. The output is formatted as if it were being printed and is suitable for loading into your word processor or desktop publishing system. By including stylesheet tags as text items within quotes in the correct places in the Query Fields line, you can preprocess query output into a form suitable for some popular desktop publishing programs. SB File Outputs a new SBase 4 file. A complete database structure with data file, definition file, and index is created automatically. See Chapter 16 Reorganizing the Database. Defining Query Output The Fields feature allows you to select the fields you wish to view or print in the query output. You can also specify other output features. - Column heading - Column position - Number of characters - Blank lines - Derived expressions - Boldface, Underline and/or Italic style - Conditional form feed - Column heading suppression - One field per line output - Inclusion of selected field list When you want to define the output of a query, follow these steps: 1. Click the Fields button. 2. Select the file from which you wish to choose fields. 3. Select the fields you want in the order you want them. 4. Include additional features in the line as you select fields. 5. Click OK to return to the main Query Definition requester. If you leave the Fields box blank, the selected fields from the current file will be output. Selecting File and Fields At the top of the requester is the name of an open file. The fields in the list box below belong to this file. You can select any open file, and then select fields from it for output in the query. 1. Click the file selector button to the right of the filename to cycle through all open files. - If only one file is open the field display does not change. 2. Select fields in the required order. - You may wish to specify output features before you select a field. See below. If you choose fields from more than one file, you are defining a Multi-File Relational Query. You will have to specify a link between the files. See Chapter 17 Multi-File Applications. When you select a series of fields one after another, SBase 4 uses commas to separate the field names. When the data is output a space is placed between fields. - If you replace the commas with semicolons, the items will be output without any spaces in between. Defining Derived Columns A derived column in the query output is one in which the data is generated from a calculation or a function, usually involving an existing field or fields, one or more arithmetic operators, and explicit values. Examples are: Multiplication: Quantity.Order * Price.Stock Fixed decimal places: FIX(amount.Order/3,2) Increase of 15%: Price.Stock * 1.15 Decrease of 8%: Price.Stock * 0.92 Future date: Date.Order + 30 Day of the week: DAY$(date.Order) Gross margin: Sales.Inv - (Cost.Inv + Commission.Inv) Concatenated fields: Firstname.Clients+" "+Lastname.Clients To set up a derived column: 1. Click on a field or an operator on the requester or: - Type a value into the Value box and press ENTER or: - Type a function keyword directly into the Fields box. 2. Continue until your expression is complete. 3. Use the AS operator to set a column heading if required (see below). 4. If necessary, add further fields or expressions. A derived column that produces a numeric result is output in the default numeric format, which is specified with the Number Format command on the Set menu. See also Defining Numeric Format, below. A derived date or time column is output according to the format specified with Date Format on the Set menu. The data in a derived text column is output at its actual length, so its length may vary. This means that if the following fields are aligned, they must be positioned by specifying a fixed column position for the next column. Defining Column Headings When you specify a field as part of the query output, SBase normally prints its name at the head of its column on each page. You can use the AS Heading button to define your own column heading text for both fields and derived columns. 1. Select the field or specify the derived column expression. 2. Click the AS Heading button. 3. Type the text for the heading into the Value box. - If you're using Query to create a new database, you must make sure that the headings for any derived columns are valid field names - legal characters only, not reserved words, and not more than 15 characters in length. See Output Destination, below. 4. Press ENTER to copy the heading text into the Fields box. Examples: Sales.Inv - (Cost.Inv + Commission.Inv) AS "Gross Margin" Firstname.Clients + " " + Lastname.Clients AS "Full Name" Code.Clients AS "Postcode" Note If you wish to remove the column headings, enter the keyword FIELD as the first item in the Fields box. Defining Column Position If you don't specify column positions for the fields, SBase 4 will output the field data for each record in a line, with each field appearing right after the previous one. Automatic positioning is useful for simple field output, but some derived columns are of unpredictable length, and you may wish to control the format manually. To define a fixed column position for a field or a derived column: 1. Click the @ Position button. 2. Type the column number into the Value box. 3. Press ENTER to copy it into the Fields box. 4. Select the field or create the derived column expression. You can use this feature to print fields from a single record on more than one line. For example, if you specify the same column position for two fields in a row, the second field will appear on the following line. See also Inserting Lines, below. Overlapping fields can cause problems. This usually occurs when you tell SBase to print a field at a column position which has already been used by the preceding field's trailing spaces. SBase's response is to print the field at the correct column position on the next line (see & Length, below). Defining Field Length for Output One solution to the problem of overlapping fields on a line is to limit the number of characters output for each field. You may also wish to do this when you only need to output the first few characters of a field. 1. Click the & Length button. 2. Type the number of characters to be output into the Value box. 3. Press ENTER to copy it into the Fields box. 4. Select the field or create the derived column expression. Defining Numeric Field Format for Output When you are outputting numeric fields, you may also use & Length to specify the number of digits that will be displayed before and after the decimal point. To do this, follow the procedure above, but enter a decimal number into the Value box in step 2. For example: &5.3 (Numfield.Order/3) Here the number 5.3 ensures that the result of the calculation (Numfield.Order/3) will be displayed to three decimal places and with five digits before the decimal point. Inserting Lines Query output defaults to a tabular format determined by field lengths. You can vary this by inserting the NEWLINE keyword into the Fields line. This instructs SBase to output the next item on a new line. Before or after any field or derived column expression: 1. Click the LINE button. 2. Repeat if more than one new line is required. Page Oriented Output By combining multiple NEWLINE keywords and the Column Position feature, you can produce page oriented output suitable for printing on pre-printed stationery. The EJECT keyword can be used to move to a new page (see below). You may also wish to suppress column heading printing with the FIELD keyword (see below). Printing Long Text Fields If you want to print text fields that are longer than can fit in a normal size column, you should follow a special procedure. 1. Place either a NEWLINE keyword or an @ positioning parameter in the Fields line. 2. Type the function keyword TRIM$. 3. Place the name of the long text field in parentheses: TRIM$(Comments.CLIENTS) SBase 4 will strip trailing spaces off the field and print it across the page, using wordwrap if required. If you wish to print the field at its full length as given in the file definition, omit the TRIM$ function. Additional Format Parameters There are a number of other parameters that can be used to specify the format for Query output. These are not shown in the Query Fields requester and must be typed directly into the Fields box with spaces or semicolons separating them from other words in the line. The following table lists all the output format parameters that are not shown in the Query Fields requester: Parameter Purpose ALL Includes the selected fields list from the current file in the output FIELD Suppresses column headings DOWN Outputs each item in an expression list on a new line BF [ON] Sets text to boldface UL [ON] Underlines text IT [ON] Sets text to italic BF/UL/IT OFF Turns selected text style attribute off ATTR OFF Turns all text style attributes off EJECT nn Outputs data on a new page if there are less than nn lines left at the bottom of the current page ALL The ALL keyword causes the current selected fields list to be included in the output. You can add fields and expressions to the Fields box if you wish. FIELD If you want to suppress the headings for all the fields in the command line, place the FIELD parameter at the beginning of the line. For example: FIELD Firstname.Clients, Lastname.Clients, Country.Clients If you place FIELD anywhere else in the command line, it will only suppress the headings for the fields that follow it. DOWN DOWN must be placed at the beginning of the Fields line, before the field names. It ensures that the data for each field is output on a new line. For example: FIELDS DOWN Lastname.Clients, City.Clients would output data like this: Arnauld Paris DOWN presents data like Record View, with leading field names. You can combine DOWN and FIELD, so that the data for each field is output on a separate line but without the field name, for example: FIELD DOWN Lastname.Clients, City.Clients STYLE PARAMETERS BF, UL and IT set the text style for output. BF sets boldface UL sets underline IT sets italic Each of these can be set by ON (which is optional) and unset by OFF. ATTR OFF unsets all the text style attributes. For example: UL IT Lastname.Clients, UL OFF City.Clients ATTR OFF The Lastname field will be printed or displayed on screen underlined, City will be italicized. EJECT You can use EJECT to ensure that all the data for a single record is output on the same page. The number following EJECT should be the number of lines occupied by the record data. Thus, if you were using two lines to output the data from each record, the Fields command line would look like this Firstname.Clients, Lastname.Clients, NEWLINE City.Clients, Country.Clients EJECT 2 SBase 4 will then display or print a new page whenever less than two lines are left on the current page. Entering EJECT on its own or EJECT 0 causes SBase to output a new page for each record. The ON File Button The ON File button is used when more than one file is involved in the query. Its function is described in Chapter 17 Multi-File Applications. Defining Report Features The Report button call up a requester which allows you to include in your query definition various features commonly found in report generators. See Chapter 15 Reporting for details. Defining a Query Filter The Filter button calls up a requester very similar to the Filter requesters used in the Print File function on the Project menu and in the browsing controls. If you don't want to restrict the records selected for the query output, leave the Filter box blank. Data from all the records in the file will be output. When you want to define a filter to select records for output, follow this procedure: 1. Click the Filter button. 2. Click the file selector button next to the filename to select the file from which you wish to choose fields. 3. Create a Filter command line. See Chapter 10 Retrieving Data Interactively and Chapter 9 Using Functions. 4. Click OK to return to the main Query Definition requester. The Query Filter works exactly like other filters, except that you may cycle through the open files by clicking the file selector button. You click a field name, then an operator, then enter a value into the Value box and press ENTER. Use AND and OR to build up complex filters. Functions should be typed directly into the Filter box. See Chapter 9 Using Functions. Note If you choose fields from more than one file, you are defining a Multi-File Relational Query. You will have to specify a link between the files. See Chapter 17 Multi-File Applications. Defining a Sorting Order The Order button allows you to choose a sorted order for the query output. You can select fields from any file, specifying either ascending or descending sorting order for each. Note You will need to have an area of free memory before you use Query Order. If you don't specify any fields to order on, SBase 4 will present the records in the order in which it finds them on the disk. Most probably, they will not be in any alphabetic or indexed order. As this is the fastest way of retrieving information from the disk, you may want to use it if the order of presentation does not matter. If you're creating a new database you should probably avoid specifying an order, to speed up the processing. However, in the majority of cases, you'll want to see the records ordered, especially when: - Indexed order, such as date order, is an important part of the file. Also, defining a sort order on a single indexed field, allows SBase 4 to make use of the index and speeds up the Query. - You have set up subtotals on the Report line. If you do want to add order features to the query, follow this procedure: 1. Click the Order button. 2. If necessary, click the file selector button next to the filename to select the file from which you wish to choose fields. Note If you choose fields from more than one file, you are defining a Multi-File Relational Query. You will have to specify a link between the files. See Chapter 17 Multi-File Applications. 3. Select a field on which you wish to sort. 4. Click either the Ascending or the Descending button. 5. Repeat steps 2 to 4 for all the fields on which you wish to sort. 6. Click OK to return to the main Query Definition requester. Setting the Number of Characters on which to Sort The default length for sorting is 15 characters per field. SBase 4 gives equal weighting to upper case, lower case and accented instances of a character. If you want a field to be sorted on more or fewer than 15 characters, use the & Length parameter; before selecting the field name: 1. Click the & Length button. 2. Type a number for the length and pressTER. For example, to ensure that the Lastname field data was sorted on the first 20 characters, you would enter: &20 Lastname Single and Multiple File Sorting Outputting records from a single file is comparatively easy, and SBase 4 performs the operation without affecting the file itself. If you specified an order using just a single index, SBase outputs the data as it finds it. But if your Order request includes several fields, you may have to wait a while for SBase 4 to create a temporary file prior to producing the query output. However, if you are using more than one file, SBase 4 has to do quite a lot more work. Although the amount of work varies depending on the precise Order line you set up, essentially it means that SBase 4 can't tell what the final order is to be until it has finished reading all the files. Sorting on a Multi-field Index If you often need to sort on more than one field, you may prefer to create a composite multi-field index, which SBase 4 will update automatically, allowing you to select it for sorting in Query Order and obtain results quickly. Such an index may be based on a Virtual field set up in the File Definition. See Chapter 2 Defining New Files and Chapter 4 Creating Indexes. Running a Query To run a query, click OK on the Query Definition requester. The default output destination is the screen. If the Set Paging menu command is checked, the output will pause when the screen is full; press the space bar to continue. Saving a Query Queries are re-usable. You can store the query instructions (except for the output destination) in a disk file, and open it when required. 1. Select the Process Query Save As command. 2. Select or enter a name for the query. 3. Click OK. SBase adds the extension SBQ and stores the query file. When you re-open it, any files that are not already open are opened automatically.