LCL005 - Build, Execute and Save a Join Query
The Sample query supplied with LANSA Client is a simple query which retrieves data from a single file. When you create a query that obtains data from more than one file, it is called a Join query. With LANSA Client you can build Join queries very easily.
The Sales Country and Product Code in the Sample query show only the codes. As these are not very meaningful, you will use the Sample query as the basis of a new Join query which will include the country and product descriptions.
The Country Description is found in the Country file while the Product Description is found in the Product file.
Before creating this Join query, close any open Query and Results windows.
1. Open the query as you did before, by selecting the menu on the and choosing the command.
2. When the dialog box appears, select the LCSample.lcq (Sample Query) and press the button, or simply double-click on the Sample Query. The sample query is opened and the definition is displayed in a Query window, as before.
When you create a query, you must first choose a file to become your primary file. In this case, the primary file is the Sales History file. You can easily identify the primary file because its name is displayed in the title bar of the area.
Once a file has been "selected" by double clicking on its file name, all the fields in the file and the files that are related to the file are displayed. Related files are defined in the Repository on the host using LANSA's Access Route Definitions. Related files are indented beneath the selected file.
3. Locate the Country file in the area of the window and select it by double-clicking on the file name.
The fields for the Country file will be immediately displayed in the area.
4. Add the Country Description to your query by dragging the Country Description from the area and dropping it into the area immediately below Sales Country.
5. Repeat this process by locating the Product file and dragging and dropping the Product Description immediately after the Product Code.
6. Include the Sales Warehouses names by dragging City from the Country Warehouses file to follow Sales Warehouse.
You have just constructed a join query which spans four files without having to specify any fields to join the files together. LANSA Client took care of the join logic for you. (If you want to see the fields which are used to join the files, select the command from the menu. The Conditions list box in the Field Conditions dialog box shows you the fields which are used to join the files.)
You can now execute the Join query.
7. Select the method of executing the query which you prefer or return to Step 3. Execute the Query in tutorial LCL002 for a reminder.
This query will run a bit slower than the earlier queries because it retrieves its data from four files.
Once the query is executed, the results are displayed in a window.
Note that the Product Description, Country Description and the City in which the Sales Warehouse is located are now displayed. Each time a record from the Sales History file is read, the corresponding country, product and city descriptions are read from their respective files.
You have just created a query that accesses four files to produce the results in a single window.
Now you can Save the Query.