LANSA Client User Guide

LANSA Client User Guide

  • About this Guide
  • Introduction
  • Getting Started
  • Creating and Running Queries
  • Working with Query Results
  • Creating Charts
  • Generating Applications
  • Automating Query Execution
  • Commands
  • Troubleshooting
  • Tutorials
  • Set Up the Tutorial Files
  • Appendix A. Error Message Codes

Edition Date March 24, 2014

© LANSA


About this Guide

This guide has been developed with end users in mind.

  • It contains all you need to know about using LANSA Client's query capabilities.
    It includes the LANSA Client Tutorials which are step by step descriptions of the most common uses of LANSA Client. It is a good place to start if you are unfamiliar with LANSA Client's features and the best way to use them. The LANSA Client Tutorials are designed to be used with either an IBM i, Windows or Linux host.

Another related LANSA Client guide is the:

  • LANSA Client Administrator's Guide
    This guide describes the options which can be selected as defaults. Specifying the defaults that suit your environment allow you to save time and maintain standards when developing LANSA Client queries. It shows you how to create and distribute a LANSA Client environment file which includes these defaults.

  • Note: For this version of LANSA Client to function correctly, you must first upgrade your LANSA host to V13 SP1 EPC131100 or later.

The LANSA Client software must be installed on each user's PC. Instructions for installing and upgrading LANSA Client are included in Appendix A. LANSA Client Installation and Upgrade in the Administrator's Guide.

Further Information

Ý LANSA Client User Guide home page


1. Introduction

1.1 LANSA Client - Discovering Information

1.2 What Can You Do with LANSA Client?

1.3 How Does LANSA Client Work?

1.4 What are the LANSA Client components?

1.5 The LANSA Object Repository


1.1 LANSA Client - Discovering Information

LANSA Client is an easy-to-use query, reporting and charting tool for accessing your corporate data on a Windows, IBM i or Linux machine.

With LANSA Client you get:

  • Familiar business terms - Repository-defined field and file descriptions presented in terms familiar to business users.
  • Report templates - to run standard reports or readily view and tailor reports.
  • File simplification - view simple consolidated (denormalized) files prepared by LANSA or use predetermined file joins to view many files as one.
  • File filtering - reduce the number of files to be viewed with file filtering.
  • Automatic joins - simply select fields from multiple files to create a consolidated report.
  • Drill downs - simply drag and drop, to access a link and drill down between files.
  • Ability to share data with other PC tools such as spreadsheets.
  • LANSA Client allows you to prototype a data warehouse by creating multi-dimensional databases on the client PC with dynamic regrouping at the click of the mouse.

Further Information

Ý 1. Introduction


1.2 What Can You Do with LANSA Client?

Without using any programming skills, LANSA Client allows you to:

  • Retrieve data from a database on an IBM i or Windows machine. Knowledge of the database or SQL is not required.
  • Specify that a query is to pause and request information before it runs (i.e. a prompt).
  • Enhance the retrieved data by sorting it, grouping it and adding totals.
  • Produce a chart using some or all of the retrieved data. LANSA Client offers a number of chart styles and you can view the same data in different chart styles simultaneously.
  • Generate and print reports.
  • Copy selected portions of the retrieved data and paste them into another application. This can be done using standard copy-and-paste.
  • Save the retrieved data as a file. LANSA Client supports various file formats including database, spreadsheet and text formats.
  • Save the query. The saved query can be used again later, with or without modification.
  • Generate stand-alone applications which incorporate a number of queries. This allows queries to be run without first starting LANSA Client and is useful if you have queries that are run frequently or need to be shipped to a number of workstations.
  • Change the retrieved data, and add or delete records. This feature is particularly useful for urgently required applications such as to record market research responses, for an application for a specific department or even for creating and changing test data.

LANSA Client provides full on-line help. This includes help about LANSA Client's facilities as well as help for the data you are retrieving from the host.

Further Information

Ý 1. Introduction


1.3 How Does LANSA Client Work?

LANSA Client has been designed to make use of your Microsoft Windows knowledge and skills.

As with other Windows based applications, LANSA Client's features are activated by commands. These commands are found on the menu bar where they are stored in logical groups.

There is more than one way of executing a command. The more commonly used commands are included as icons on the tool bar. If you prefer, you can also use accelerator keys (sometimes called short cut keys) to initiate a command. The keys to use are shown with each command.

You create, or define, a query in the Query window. When defining your query, you can add selection criteria in order to limit the data retrieved, you can specify the sequence in which you want the data presented, the totaling you would like, and so on. The data retrieved is displayed in a Results window unless you have directed it to be immediately displayed in another medium, such as a report.

When the data is displayed in a Results window, you can further manipulate it by changing the sequence, grouping it, adding more totals, collapsing and expanding it.

You customize charts in the Chart window. You can also select and save your own specific chart preferences, so that new charts require less or no changes.

The data retrieved by a LANSA Client query does not need to originate from a LANSA application. If it is maintained by a non-LANSA application, it needs to be "made known" to the LANSA Repository when the Repository is installed on the host.

Further Information

Ý 1. Introduction


1.4 What are the LANSA Client components?

As well as its powerful query facilities, LANSA Client incorporates:

  • LANSA Open, LANSA's intelligent middleware, to provide high speed retrieval of host data.
  • 1.5 The LANSA Object Repository, to provide security and to protect the integrity of your corporate data.

Further Information

Ý 1. Introduction


1.5 The LANSA Object Repository

The LANSA Object Repository provides significant advantages to client/server applications because it centralizes the information about the fields and files which make up the database. By storing information such as business rules, field descriptions and particularly help text in the LANSA Repository, you will find that query development time and end user support calls are reduced.

Whether you are using LANSA Client with an existing LANSA or non-LANSA application, check the LANSA Repository features before making changes to enhance the existing application and its files prior to end user use with LANSA Client. The LANSA Repository's facilities may reduce or even eliminate many changes which may at first appear to be necessary.

How to get started with the Repository, and how to enhance your files using LANSA Repository features are included Building the LANSA Repository in the Introduction to LANSA for IBM i Guide.

Because LANSA Client uses only data protected by the Repository, quality applications to edit the data can be produced quickly, with the assurance that the business rules defined for the data will not be violated.

Rules in the LANSA Repository cannot be changed through LANSA Client.

Further Information

Ý 1. Introduction


2. Getting Started

Before you can use LANSA Client:

  • LANSA Client must be installed on your workstation.
  • you must have a LANSA Client environment on your workstation. The LANSA Client environment contains the default options used by LANSA Client. How to create a LANSA Client environment is described in Administrator Tasks.
  • your chosen communications software must be started and a connection to the host established.
  • the LANSA software must be installed on the host, which will be an IBM i, Windows or Linux machine.

Note: You are responsible for installing and configuring the required communications support.

Further Information

2.1 Start LANSA Client

2.2 Host Selection


2.1 Start LANSA Client

Before you start LANSA Client, note that you need:

  • a LANSA Client environment installed on your workstation (How to create this environment is described in Create a LANSA Client Environment in the LANSA Client Administrator's Guide).
  • communications with the host established.

1. Press the Start button on the Task Bar.

2. Select All Programs, then select LANSA Client sub menu.

3. From the LANSA Client sub menu, select LANSA Client to open the software.

4. Either the 2.4 LANSA Client Alternate Window, the 2.2 Host Selection window or the 2.3 LANSA Client Main Window will be displayed.

Further Information

Ý 2. Getting Started


2.2 Host Selection

If your LANSA Client environment includes access to more than one host, you will be presented with the Select Host dialog box when:

  • you start LANSA Client, or,
  • you select the Connect command from the Host menu bar of the LANSA Client Alternate Window.

Select the host you wish to connect to. LANSA Client will connect to the host you have selected.

If the connection between your workstation and the host is successful, the 2.3 LANSA Client Main Window will be displayed. If a connection cannot be established, the LANSA Client Alternate Window will be displayed.

Further Information

Ý 2. Getting Started


2.3 LANSA Client Main Window

When you first start LANSA Client and you have successfully connected to the host, the LANSA Client main window is displayed. From this window you select and use the LANSA Client facilities, such as to create a query, run a query and so on.

All LANSA Client windows have the standard Windows facilities which include:

  • Minimize and Maximize Buttons on the upper right hand corner of the window.
  • System Menu from the icon in the upper left hand corner of the window.
  • Title Bar at the top of the window.
  • Menu Bar (or Action Bar) just below the Title Bar.
  • Tool Bar just below the Menu Bar. The purpose of each tools is displayed when you rest your cursor on it.
  • Status Bar at the bottom of the window displays:
  • The file and library to which you are currently connected
  • The Host System to which you are currently connected
  • The Status of your Query's execution

Further Information

2.3.1 What LANSA Client Help is available?

Ý 2. Getting Started


2.3.1 What LANSA Client Help is available?

There is more than one way to get help when you are using LANSA Client.

Help about the LANSA Client software

If you want to know just what a feature of the LANSA Client software will do or why it has behaved in a certain way, press the Help button if there is one on the dialog box - or place your cursor on the item for which you want help and press the F1 key.

The LANSA Client help is opened at the page that you have requested. You can move around the help file using the links provided on each page.

Help about the data in your database

The question mark help will provide you with help about the data you are using in your queries, (i.e. records and fields). To use the question mark help:

1. Click on the [image] icon in the toolbar or press Shift + F1 keys together. You will see a question mark attached to your cursor

2. Click your cursor on the item you wish to query.

The help text, if any, is displayed in a popup window. Help for you data comes from the Repository in which your data is stored. It may tell you how it is created and used, but it does not relate to any actions LANSA Client might take with it.

Further Information

Ý 2.3 LANSA Client Main Window


2.4 LANSA Client Alternate Window

This window is displayed when:

  • LANSA Client cannot connect to the host system when it starts.
  • you have disconnected from the host system you have been working with.
  • you have pressed the Cancel button on the Host Select dialog box and put LANSA Client into a "disconnected" state.

From this window you can either:

  • exit from LANSA Client by selecting the Exit command from the File menu.
  • connect to a host system by selecting the Host menu and choosing the Connect command and following the 2.2 Host Selection procedure.
  • force a refresh of the Local Data Dictionary by selecting the File menu and choosing the Refresh Local Data Dictionary option. If you choose this option, the Refresh Local Data Dictionary dialog box is displayed so that you can select the Local Data Dictionary that you wish to refresh.

Further Information

2.4.1 Refresh the Local Data Dictionary

Ý 2. Getting Started


2.4.1 Refresh the Local Data Dictionary

To refresh the Local Data Dictionary, select the File menu from the LANSA Client Alternate window and choose the Refresh Local Data Dictionary item. You should do this refresh before you select a Host.

Select the Local Data Dictionary (LDD) that you wish to refresh and press Refresh.

If you wish to refresh more than one LDD, but not all of them, hold down the Control key while selecting the LDDs you want refreshed, then press the Refresh button.

If you wish to refresh all the LDDs, press Refresh All.

Press Exit when the refresh is finished.

Further Information

Ý 2.4 LANSA Client Alternate Window


3. Create and Run Queries

Before you create a query to retrieve data from the host, you must first identify the files and fields (i.e. objects) that you need. Once you have done that, in the Query window, you simply point and click with your mouse at the objects you want to include in your query.

To help you more easily identify the fields and files that you can include in your query, LANSA Client displays their descriptive names rather than their "object" names. This is because Object Names are usually less meaningful. The Object Names can be displayed, if you choose to do so.

Further Information

3.1 Steps to Create a Query

3.2 What is in the Query Window?

3.3 The Files to Select From

3.4 The Selected Files and Fields Area

3.5 The Defined Links to Queries Area

3.6 Sort Your Data

3.7 Select Only Specific Data

3.8 Select Date & Time Format

3.9 Group Your Data

3.10 Summarize Your Data

3.11 Performance Considerations

3.12 Open an Existing Query

3.13 Run a Query

3.14 Save a Query

3.15 Cancel a Query

3.16 Delete a Query


3.1 Steps to Create a Query

These are the steps you will follow to create most queries.

1. Start with a new Query window by selecting the New command in the File menu by using this [image] toolbar icon. The Query window's contents are described in detail in 3.2 What is in the Query Window?

2. Choose your query's Primary File. How to do this is described in 3.3 The Files to Select From.

3. Select the fields you want in your query from your primary file and any other files.

4. Consider whether you can improve your query by adding sort and selection criteria or by specify grouping and summary totals. Refer to 3.1.1 Enhance your Query for some suggestions.

5. Specify where you want your query's output to go.

6. Specify any special output requirements, such as the number of records to be retrieved.

7. Check that your query will perform as efficiently as possible by answering the questions in 3.11 Performance Considerations . Make any necessary changes.

8. Save your query at this point. You can run your query without first saving it, but this is not recommended.

9. Run your query.

10. Check that the results meet your requirements.

11. Change the summarizing, grouping or sorting criteria if you need to.

12. Create a report if you haven't already got one or chart your results, as required.

13. Save your chart, report and query, if required.

The steps are the same to create any type of query. For linked queries, you just need to create more than one query and add a step which links the queries together in the required hierarchy.

Detailed instructions for creating a simple query, join query or a linked query are included in The LANSA Client Tutorials.

Further Information

3.1.1 Enhance your Query

Ý 3. Create and Run Queries


3.1.1 Enhance your Query

Unless you add some sorting or grouping criteria to your query, the data you will receive will be a continuous list of records containing the fields you have requested, in the sequence in which it is stored in the primary file on the host. In some cases, this may be suitable for your purposes, in others, the data may be more useful if you sort it and, if suitable, add selection criteria to eliminate any unwanted data.

To specify selection and sorting criteria for a query, use these commands from the Options menu:

  • Sort options, to specify the sequence.
  • Field conditions, to specify the selection criteria.
  • Group by, to split your data into groups.
  • Summary options, to add totals.

Except for field conditions, you can use these commands when you are creating your query as well as after the query has been run, when you are viewing the results in the Results window.

Unless you are unsure of what your query will retrieve, your query will run quicker if you assign these options before running the query.

If you change a query's conditions after you have run it, you will be asked if you want to save the new criteria before you close it.

Note that "fields" are referred to when you are working in the Query window, but "columns" when you are working in the Results window.

Further Information

Ý 3.1 Steps to Create a Query


3.2 What is in the Query Window?

The Query Window consists of three panes or areas. These are:

  • 3.3 The Files to Select From
    This is the area from which you will select the files and fields for your query.
  • 3.4 The Selected Files and Fields Area
    This area shows you the fields that have been selected for the query.
  • 3.5 The Defined Links to Queries Area
    This area shows the queries that have been linked.

Each area can be resized to the size of your choice.

You can display the object names as well as the descriptive names as described in 3.3.1 Display the Object Names.

If you prefer not to use the mouse pointer, you can switch from one area to another using the Tab key.

Further Information

Ý 3. Create and Run Queries


3.3 The Files to Select From

When you select the New or Open command from the File menu, the names of all the files that you can access from the host to which you are connected are displayed in the Selected Files area.

If there are too many files displayed, you might consider limiting them by creating a file filter as described in 3.3.3 Limit the Files Displayed.

Your authority to use specific files is defined in the LANSA Repository at the host. Your LANSA Client User ID is used as your identity by the Repository.

Logical files, if displayed, appear immediately after their Physical file.

The file icon is a folder. It is closed when it has not been selected for a query and open when it has been selected. You can identify the different types of files by the color of their icons, which are:

Yellow for Physical files.

Green for Logical files

You can suppress the display of logical files using Display Logical Files in the Options menu. Because a logical file may represent a faster access to your data, therefore improving the performance of your query, you should only switch off the display of logical files if you know that you will not be using any of them.

Regardless of the status of the Display Logical Files command, LANSA Client will always display the logical files which are defined as a link (Access Route) to another file.

Note: Whether or not to display logical files can also be set as a default using the Specify the Host and Related Options described in the LANSA Client Administrator Guide.

Further Information

3.3.1 Display the Object Names

3.3.2 Ordering the Files

3.3.3 Limit the Files Displayed

3.3.4 Select Fields

3.3.5 Help for Your Fields

Ý 3. Create and Run Queries


3.3.1 Display the Object Names

Files and fields are always displayed with their descriptive names and optionally with their object names in brackets at the end of the descriptive name.

You can suppress or display the object names by selecting the Options menu and choosing the Display Object Names command.

Note: Whether or not to display the object names can also be set as a default using the Specify the Host and Related Options described in the LANSA Client Administrator Guide LANSA Client Administrator.

Further Information

Ý 3.3 The Files to Select From


3.3.2 Ordering the Files

Sometimes the list of files can be quite long, which makes finding the file you want quite difficult. In addition to the File Filter option, you can use the Order Files By command in the Options menu to change how the files are sorted.

Order Files By has a submenu with three options:

Nothing - no sorting will be applied.

Description - sort by File description.

File Name - sort by File or Object name.

Further Information

Ý 3.3 The Files to Select From


3.3.3 Limit the Files Displayed

If the Files to Select From area contains too many files, you can display a subset of them using the File Filter Options dialog box which is displayed when you select the File Filter command on the Options menu.

Filter

Enter a string of characters to select the files to be displayed. This character string is called the filter.

You can specify a partial string and include the '*' or '%' wildcard characters anywhere in your string.

For example, if you want to see all files with the characters 'NAME' somewhere in their name, you would specify '*NAME*' as the filter.

Display files with matching file names

If your filter is to select files by the file's object name, select the Display files with matching file names option, otherwise files with matching descriptive names will be selected.

Use case sensitivity

If you want to select only files that match the case of the characters in your Filter, select the Use case sensitivity option, otherwise both upper and lower case names will be selected.

Saving your Filter

When you are satisfied with your filter specifications, select the OK button or press the Enter key. The Query window will be refreshed to display only the files which match the filter.

If you want to discard the filter, then select the Cancel button or press the Esc key.

Further Information

Ý 3.3 The Files to Select From


3.3.4 Select Fields

The fields which make up a file (i.e. the file definition), as well as all the other files that you can access from that file are displayed when you select a file.

As well as data fields, there will be key fields and perhaps virtual fields displayed. You can identify the different types of fields by their icons, which are:

A key for key fields

Yellow for data fields

Blue for Virtual fields

When you have selected a field for inclusion in your query, its icon is overlaid with a tick.

To select a field for your query, use one of these methods:

  • Drag the relevant field from the Files to Select From area into the Selected Fields area, using the standard Windows drag and drop technique. Dragging and dropping individual fields allows you to place them in the sequence you require, as you go along.
  • Double-click on the field.
  • Press the Enter key while the field is highlighted.

To select all the fields in a file:

  • Drag and drop the file from the Files to Select From area into the Selected Fields area.

You can improve the performance of your query by using the different types of fields for different purposes. For example, your query will run much faster (this can be up to ten times faster) if you add selection criteria to the key fields, rather than to the data fields.

Virtual fields are fields which do not physically exist, but are calculated whenever they are required. For example, your file may contain a person's date of birth, but not their age. If the age is defined as a virtual field and if you include age in your query, then it is calculated each time that person's record is retrieved. This means that if you include the age in your query, it may take a longer time to run. It is better not to include virtual fields in a query unless you need them.

Refer to 3.11 Performance Considerations for tips about the best use of the field types.

Further Information

Ý 3.3 The Files to Select From


3.3.5 Help for Your Fields

When you are building a query or looking at the results, you can obtain help for any of the fields in your files.

To obtain this Help, hold down the Shift key and the F1 key at the same time or press the [image] icon in the toolbar. Slide your mouse pointer to the item for which you want help. When you release the mouse button, LANSA Client will retrieve the related help text from the LANSA Repository and display it in a window.

Further Information

Ý 3.3 The Files to Select From


3.4 The Selected Files and Fields Area

Selected Files

The Selected File area of the Query window shows you the file used to create a query, and any fields and linked files available to add to the query.

You can select fields & linked files individually by either double-clicking them or dragging them across to the Selected Fields area, or you can select the file in order to use all of its fields.

You can identify the different types of fields by their icons, which are:

A key field

A physical data field

A virtual data field, usually calculated from values in other fields

Linked Files use the following icons:-

A key field. A file with a one-to-one record relationship with the parent file. When selected, its icon will change to [image].

A file with a one-to-many record relationship with the parent file. When selected, its icon will change to [image].

A logical file (or view) with a one-to-many record relationship.
When selected, its icon will be [image].

When you click on either a file or field, the status bar will display descriptive information, such as (in the case of a field) its data type, maximum size and any format rules.

Selected Fields

The Selected Fields area of the Query window shows you all the fields that have been selected for the query.

Fields can be selected from a number of files. You do not need to know which file a field belongs to, because LANSA Client automatically handles this for you.

To remove a field:

  • Drag the field out of the Selected Fields area and drop it.
  • Double-click on the field.
  • Press the Enter key while the field is highlighted.

Note: If you are removing a field which has a condition attached, remove the condition BEFORE you delete the field, otherwise you may find that any remaining conditions do not operate as expected.

To re-sequence the fields you have selected in the Selected Fields area, simply drag each field from its current position and drop it into its required position.

When the query is run, the fields are retrieved from the host in the sequence that they are listed in the Selected Fields area.

Further Information

Ý 3. Create and Run Queries


3.5 The Defined Links to Queries Area

The Defined Links to Queries area of the Query window shows all the files which you have linked together.

To indicate that a query is a linked query, you drag your primary file from the Selected Files area and drop it into this area. Then you drag the file of the secondary query into this area where it will be indented beneath the primary file. You can attach more than one secondary query to a primary query.

Linking queries in this way enables you to drill down into your data, allowing you to start from one query and end up viewing data from other related queries.

Further Information

3.5.1 Rename Linked Queries

Ý 3. Create and Run Queries


3.5.1 Rename Linked Queries

The queries in the Defined Links to Queries area are initially identified by their file name. You can change this name to one that is more meaningful if you double click on query to be changed. The Link Query Options dialog box is displayed.

Query Description

Enter the Name you wish to assign to the query you have selected. The new name will be displayed in the sub-menu of the Linked Queries command when you run the linked query.

Report File name

Normally, a report's file name should be the same as the query name. If it is different, you can either enter the name of the report you wish to attach to this query or press the Browse button to browse through your files to choose the required report.

This option can be used to link any type of query to a report with a different file name. This dialog box can be reached by selecting the Linked Query Option from the Query menu.

Saving your Options

Press OK to save your selection or Cancel to escape.

Further Information

Ý 3.5 The Defined Links to Queries Area


3.6 Sort Your Data

To make your query's results easier to read, you may wish to sort the data into a more useable sequence.

You can specify your sorting requirements either from the Query window, before the query is run, or from the Results window after the query has been run. There are advantages to sorting your data before you run the query. These are explained in 3.11 Performance Considerations.

To specify your sorting requirements, select the Sort Options command from the Options menu or select this [image] icon from the toolbar.

When you select this command, the Sort Options dialog box appears.

The Sort Options dialog box contains:

Field

A list of the fields you can sort on. You can include more than one field in a sort.

Sort Type

The sort type indicates that you wish to select the corresponding field for sorting. Choose the sequence you want by clicking on the arrow to the right of this box and select either:

  • Ascending, so that the results are returned in the sequence lowest to highest.
  • Descending, for results in highest to lowest sequence.
  • None will remove a field from the sort.

Display field in results

A tick in this box indicates that the field will be displayed in the results.

For example, your query may include a "Name Sequence" field which is used to sequence your customer names but you only wish to see the customer's name in your results. In this case, you would specify ascending or descending Sort Type for the Name Sequence and remove the tick from the corresponding Display field in results box.

As another way of using this option, imagine that you have defined a query which retrieved Field1, Field2 and Field3. Having seen your results, you decide that you only want to chart the results from Field1 and Field3. Rather than redefine and rerun the query to retrieve only Field1 and Field3, turn off the display of Field2 using this Display field in results option. Then you can simply chart the results from Field1 and Field3.

Note: You cannot turn off the display of a field that has Group or Summary options added to it.

Re-sequencing your Selected Fields

You can re-arrange the order of the fields in a sort by highlighting a field and selecting either the Up or Down buttons located at the right side of the dialog box.

For example, your query may consist of FieldA, FieldB and FieldC and you want to sort your data using FieldC then FieldB. In this case, you would attach the required Sort Type to Fields C & B, then re-arrange the fields by highlighting FieldC and using the Up button to move it above FieldB in the Field list.

Saving your Sort Options

Once you are satisfied with your sort options, select the OK button. If you want to ignore any changes you have made, select the Cancel button.

Select the Reset button to remove all the sorting criteria.

Note on Sorting Data

If you have specified a sort for your query, LANSA Client will determine whether it can sort your data at the host rather than at your workstation.

Depending on where it is sorted, the result may be different.
For example if, your data is sorted into ascending sequence on the IBM i, numeric data will follow alphabetic data. If the same data is sorted into ascending sequence on a workstation, the numeric data will come first. The reverse will apply with descending sequence.

Sometimes you will want to control where your data is sorted. As a general rule, LANSA Client will sort the data at the host if:

  • your query is a join query.
  • you have attached selection conditions to non-key fields and there are no virtual fields defined in your query.
  • your query contains a LIKE selection condition.

The result of your query may vary when the Maximum Record number is used to limit the number of records retrieved. To avoid any variations, you can either run your query using a logical file which is already sorted into the sequence you want, (the first 'n' records are always retrieved) or define your query so that the sorting is always done at the host.

Top-10 Style Query

By using a combination of Sort options and Maximum records, you can create a Top-10 style query. You could use this style of query to obtain, for example, the ten salesmen with the highest sales or your 6 best selling products. To do this, you need to ensure that the data is sorted in descending sequence on the IBM i. To ensure that the data is sorted on the host, either create a join query or use the Like operator with an All Values prompt, and use the Output Options command to set the Maximum records to 10, or the number you require.

Further Information

Ý 3. Create and Run Queries


3.7 Select Only Specific Data

The Field Conditions command allows you to select only the data that you require. For example, you may wish to obtain a list of employees who have commenced employment after a particular date or to find all customers with names commencing with the letters A through to E.

Because you only retrieve the data you require from the host, this command can only be used when you are defining your query in the Query window.

When you select this command from the Options menu or via this [image] toolbar icon, the Field Conditions dialog box appears.

Enter the

The Field Conditions dialog box contains three main areas. These are:

  • 3.7.1 The Operation Area
    In this area you select a field in your query and specify the selection criteria you want to attach to it.
  • 3.7.2 The Prompting Area
    This area is used to specify that you want the query to pause so that you can enter selection criteria each time the query is run. For example, to specify the date or period for which you require a list of sales.
  • 3.7.3 The Conditions Area
    This is a list of the conditions that have been specified for the query in the sequence in which they will be processed by the host. Each item is treated as an individual search condition unless it is within brackets, in which case it is assumed to be in a "group" and is evaluated accordingly.

Further Information

3.7.1 The Operation Area

3.7.2 The Prompting Area

3.7.3 The Conditions Area

3.7.4 Add a Search Condition

3.7.5 Change a Search Condition

3.7.6 Delete a Search Condition

Ý 3. Create and Run Queries


3.7.1 The Operation Area

Use the options in this area to specify your selection requirements.

Selected fields

Select the field to which you wish to attach a search condition (i.e. an Operator). Take care when choosing this field, as choosing the right type of field can really affect the performance of your query.

For example, by selecting a key field instead of a data field, your query can run up to 10 times faster. You can recognize key fields, as they have a key icon in the Query window.

More than one search condition can be added to a field, although each condition must be defined separately.

Note: Do not select a virtual field here. If you add a search condition to a virtual field, it will degrade the performance of your query. (The icons of virtual fields are colored blue in the Query window.)

Operators

Select one of the operators from the drop down list. You can select:

Equal To

Not Equal To

Like

Is In (Contains)

Less Than

Less Than or Equal To

Greater Than

Greater Than or Equal To

For Range or Between conditions, combine Greater Than or Equal To and the Less Than or Equal To.

Using AND/OR/Not

You can further refine your search conditions using the AND/OR/Not radio buttons.

For example, select the Not operation to retrieve data not equal to your search condition.

Quite precise search criteria can be specified by grouping two or more of these conditions together. Refer to 3.7.3 The Conditions Area for an example and details about grouping your conditions.

Value

The value to use with your search condition.

Data will be selected if it matches the value entered. If you are not sure of the actual length or composition of the value you can enter, a description is provided beneath the Value box for guidance. Your value cannot be longer than the field you have selected.

If you use the Is In Operator, each value you enter must be separated by a comma. Your Value can be a maximum of 1023 characters. To enter Values longer than that, you can use OR to link multiple Values.

You can insert All values here, if the value is to be entered via a Prompt dialog. This means that your query will pause and wait for a value to be entered in the Prompt Condition dialog box.

If you want to select all values and you are not using a Prompt, then you should use the Like operator instead of the generic "All values".

When you use the Like operator, you can use these wildcard characters in your value:

  • % or * to indicate more than one character.
  • ? or _ to indicate a single character.

For example, if your Value is:

Wils% or Wils* | you will retrieve Wilson, Wilshire, Wilshaw, Wilsmore, etc.
Wils % or Wils * | you will retrieve Wils John, Wils Duncan.
Wil_s% or Wil?s* | you will retrieve Wills, Wilesley, Wilms, etc.
%Wil% or *Wil* | you will retrieve names which have Wil somewhere in them, such as David Willis, Jo O'Wilket, etc.

LANSA Client will append the % character to the end of the value entered if the field is longer than the value you have entered and if you haven't used a wildcard character.

If the value can vary each time the query is run, for example you want to select sales by date, enter a default value here and specify in the Prompting area that you want to enter a Value when the query is run.

Make condition case sensitive

Ideally, it is better to select alphabetic data which exactly matches the case of the Value you have entered.

For example, if your Value is to select product code ABC% or ABC*, your query will run faster if the Product Codes in the database are all in upper case, that is ABC123.

Deselect this option if you do not want the selection to match the case of the value entered. In this case, for Product Code ABC% or ABC*, you could retrieve product codes ABC123, abc123, Abc123 and so on. By deselecting case matching, your query will take longer to execute. As a rule, you should only turn this feature off if you really have to.

Note: Some databases are case-insensitive, and upper and lower case characters are treated as equivalent. For these databases, the case of the Value entered is ignored when retrieving data. For example, if you entered ABC as the Value, with an Operator of 'is like' and with the Make condition case sensitive option selected, you could receive back AbCd, ABCD, abcd and so on. This is normal behaviour for case-insensitive selection. Your database administrator will be able to advise you if your host database is of this type.

Date and Time Entries

To the right of the Value, is a button labelled "...".

Clicking this will open the DateTime Selection dialog, where you can select a date from a Calendar and enter Hour, Minute and Second values.

For more information, refer to DateTime Selection.

Further Information

Ý 3.7 Select Only Specific Data


3.7.2 The Prompting Area

When this option is used, whenever the query is run, the query pauses and LANSA Client displays a Condition Prompt dialog box asking for a Value to be entered.

A single query can include search conditions which require a prompted value as well as conditions which don't require a prompt.

Prompt for value when query is executed

Select this option to prompt for a value when the query is executed.

Prompt Text

In up to 40 characters, enter the text to be displayed in the Condition Prompt dialog box. For example, "Enter the month to be reported".

If no text is specified, LANSA Client will use the descriptive name of the field to build prompt text, such as: "Please enter a value for ".

How It Works

A Condition Prompt dialog box is displayed whenever a query with this prompt is executed. As this box is dynamically created for each query, the contents will vary depending on the query's conditions. Each Value entered in this dialog box replaces the Value entered in the Operation area.

The Value entered in the Operation area is displayed initially. An All Values entry to retrieve all the data can also be selected from the drop down list.

Enter the value you require and select the OK button to continue running the query.

Further Information

Ý 3.7 Select Only Specific Data


3.7.3 The Conditions Area

This area lists the conditions you have created for your query. To see how the conditions you have specified will work, try to step through them using "dummy" data on paper, rather than using a "try running and see" approach.

Showing Conditions in Descriptive Text

Search conditions are normally listed with the descriptive names of the fields as these provide a more readable format.

If you would rather see the field's object name, disable this option.

Grouping Search Conditions

Some conditions need to be processed as a group to achieve the required result.

For example, if you define these conditions to select all employees who have worked more than 60 hours or less than 23 hours in a specific week:

Time Equal To Week 46

AND Total Hours Greater Than 60

OR Less Than 23

The employees who have worked more than 60 hours in week 46 and the employees who have worked less than 23 hours in any week would be selected.

However, if you specify that these conditions are grouped, you achieve the required result:

Time Equal To Week 46

AND (Total Hours Greater Than 60

OR Less Than 23)

Specifying Grouped Conditions

1. Hold down the Ctrl key and highlight the conditions to be grouped in the Conditions list.

2. Select the Group button.

3. LANSA Client will immediately place brackets around the grouped conditions. They will also be indented, to show you that they belong to the same group.

Naturally, you can only group conditions together if they are listed sequentially in the Conditions list.

To ungroup conditions, simply select the conditions you want to ungroup and select the Ungroup button. LANSA Client will immediately re-assign the brackets and remove the grouping indentation.

Note on File Joins: When your query contains fields from multiple files, the join conditions LANSA Client will be using are displayed in this area in upper case.

LANSA Client handles the join conditions between the files for you, so you do not need to understand the file names displayed.

You cannot modify or delete any of the joined files listed.

Further Information

Ý 3.7 Select Only Specific Data


3.7.4 Add a Search Condition

1. Select the required field from the Selected fields list.

2. Select the operation from the Operators list.

3. Enter the search value in the Value entry field.

4. Add an AND/OR/Not option if required.

5. Select the Add button in the Conditions area. The new condition will be immediately listed in the Conditions list. If you wish to discard the selection before it is added, select Cancel button.

6. Once you are satisfied with all the search conditions you have specified, select the OK button.


3.7.5 Change a Search Condition

1. Select the condition in the Conditions list. The details will be displayed in the Operation area at the top of the dialog box.

2. Modify the options as required.

3. Once you are satisfied with your modifications, select the Change button to instigate the change. The entry in the Conditions list will be immediately updated to reflect the changes you have made.

Do not forget to select the Change button after you have modified a search condition, otherwise your changes will be ignored.

Further Information

Ý 3.7 Select Only Specific Data


3.7.6 Delete a Search Condition

1. Select the condition in the Conditions list.

2. Select the Delete button.

The selected condition will be immediately removed from the list.

Further Information

Ý 3.7 Select Only Specific Data


3.8 Select Date & Time Format

The DateTime format dialog, selected from the Options menu or via the [image] toolbar icon, allows you to choose how to display your dates and times and the format in which you wish to save them in Excel, dBase, MS Access files.

To open the DateTime format dialog box, select the command Selecting Date Format for numeric field and date type for output files from the Options menu.

Field

A list of the fields that you have selected that may be date or time fields. These fields have been recognized as dates and/or times by one of the following:

  • Numeric fields that have editcodes 'Y' or 'W' attached
  • Numeric fields that have a specific editword matching those listed in the LANSA Client Configuration file as indicating dates or times at your site. These edit words are recorded by the LANSA Client Administrator.

Date Format

From each field's drop down arrow, select the format in which each date is stored in the database. You must select the format defined for the original date. If you don't select the correct format, the display of the entry will not be correct. No validation is performed on the incoming data. If the data does not match the nominated format, incorrect results or conversion errors will occur.

Save as Type Date or Time in Excel
Save as Type Date or Time in dBase
Save as Type Date or Time in MS Access

If the item in Field is a date, datetime or time, then insert a tick in the box for the relevant file format.

Press OK to save the options you have selected.

Further Information

Ý 3. Create and Run Queries


3.9 Group Your Data

A "group" of data is a set of records which relate to each other in some way. For example, employees could be related by Cost Center or Department.

You can specify that you want your data in groups either before you run your query or while you are looking at the results in the Results window.

For example, you may run a query to list all employees due for a performance review by a particular date. The query may have originally grouped its results by region, but this grouping may now be unmanageable, so it needs to be broken down into smaller groups, such as cost center. To do this, you can specify the new grouping when you get the results, rather than changing the query's definition and rerunning it. By also specifying Summary Options and linking them to the groups, you could include, for example, the number of employees and the average salary for each cost center.

Grouping criteria is specified in the Group By dialog box. This is reached by selecting the Group By command in the Options menu or via this [image] toolbar icon.

The Group By dialog box contains two main areas. These are:

  • Query Fields
    A list of all the query's fields.
  • Group By fields
    A list of the fields by which the results will be grouped.

How It Works

From the Query fields list, select each of the fields by which you want to group your results and press the Add button. Alternatively, you can double-click on the required field in the Query fields list. The field(s) you have selected for your grouping condition will be displayed in the Group by fields list, in the sequence you have selected them.

If you want to resequence the fields, simply highlight the field to be moved and select the appropriate Up or Down button on the right hand side of the dialog box.

To remove a field from the Group By fields list, highlight the field and then select the Remove button.

To remove all the grouping, select the Remove All button.

Summary Options apply to grouped fields

If you want the summary options that you specify to be applied to your groups, select this option.

Saving your Grouping Options

When you are satisfied with the Groups you have selected, save them by selecting the OK button or pressing the Enter key, otherwise select the Cancel button or press the Esc key.

Further Information

Ý 3. Create and Run Queries


3.10 Summarize Your Data

Totals or summary values can be calculated for the data (columns) you have selected.

For each total or summary, a row with a label instead of a row number is added to the data in the Results window. For example, an 'Average' row will be labeled Avg. These rows are colored blue for group totals and green for the final total. Totals are provided for groups only if the Summary Options apply to grouped fields option was selected with the query's grouping.

You can add summaries to a query when you are defining it in the Query window or when you are viewing the retrieved data in the Results window.

For example, you could request the average value of sales by sales area when you are defining the query. When viewing the results you could decide that you would also like to see the total number of products sold as well as the average sales value. Simply add the extra summary options and the new summary rows will be immediately inserted into your Result window. There is no need to rerun the query.

Note: You cannot save the totals that are shown in your Results window. You can only save the raw data.

To specify the summaries required, select the Summary Options command in the Options menu or the [image] toolbar icon. The Summary Options dialog box is displayed.

The Summary Options dialog box contains two main areas. These are:

  • Field
    A list of the fields you have selected for the query.
  • Summary Option boxes
    You can select one or more of these options for each numeric field:

Minimum | For the lowest number in the field/column.
Maximum | For the highest number in the field/column.
Average | The average value of the field/column.
Sum | The total of the field/column.
Count | The total number of occurrences of the field. Count can be used with alphanumeric as well as numeric fields.

Where a specific type of summary can be calculated, there will be a small button in the center of the summary option's box. Select the option you require for each field. When you select an option, a tick is inserted in this small box.

The small button will not be present when a summary cannot be calculated, such as when a field is alphabetic.

Saving your Summary Options

Once you are satisfied with the summary options you have selected, select the OK button. Otherwise, select the Cancel button or select the Reset button to remove all the summaries and totals.

The grouping and summary options you specify will be saved with your query's options when you save the query.

Further Information

Ý 3. Create and Run Queries


3.11 Performance Considerations

Now that you have defined your query, you should check the points which may improve its performance.

For example:

Are there any virtual fields that you don't need in your query?

If there are, remove them from your list by moving them out of the Selected Fields area.

Have you added any conditions to a virtual field?

If you have, could you use a key field or data field for this condition instead?

Is your sorting criteria only attached to key fields?

If not, could you change the sorting criteria to sort on key fields and still obtain the data in the sequence you require?

or

Is there a logical file which you could use to obtain the data in the sequence you require?

Have you attached field selection conditions to the secondary file of your query?

Your query will run faster if you select from fields in your primary file, rather than your secondary file. If you must select conditions in your secondary file, you might consider re-organizing your query to switch the files.

For example, imagine that you want to retrieve Sales for a specific Region. You could have defined the Sales file as your primary file and the Sales Area file, which contains the Sales Area descriptions, as your secondary file. If your Sales file contains the Sales Area Code and your Sales Area file contained the Region Codes, then your query would run faster if your Sales Area file was the primary file.

You may also find that your query runs faster if you use the smaller file as your primary file.

Is the value you have entered in the same case as the data you are selecting?

Selecting data which exactly matches the Value you have entered will help your query run faster. If you are not sure of the case (upper or lower) of the data that you are looking for, run your query to retrieve a limited number of records to see what is retrieved. This may enable you to change the Value to match that of the data. To limit the number of records retrieved by your query, select the Output Options command in the Query menu.

Further Information

Ý 3. Create and Run Queries


3.12 Open an Existing Query

To open an existing query to check its definition or to change it, you can:

  • select it from the list on the File menu of the last four queries you have saved,

or

  • select the Open command from the File Menu or use this [image] toolbar icon to open the Open Query dialog box.

This dialog box displays a list of all the existing queries.

From the list, select the query you wish to open. You will notice that the query's description and file name are immediately displayed in the Query Description and File Name boxes.

Select OK to open the Query Window for the selected query, otherwise press Cancel to escape.

Further Information

Ý 3. Create and Run Queries


3.13 Run a Query

When you run or execute a query, LANSA Client uses the query's definition to retrieve the required data from the host. If you are running a query created with a Crystal Report version prior to Crystal Reports XI, you may encounter a problem related to the data's location. Refer to Troubleshooting - Crystal Reports for the steps required to fix the problem.

You do not have to open a query to run it.

To execute an open query from the Query window:

  • Select the Execute command from the Query menu or use the [image] toolbar icon.

To execute a query which is not open:

  • Select the Run command from the File menu or use the [image] icon on the tool bar

and

  • Select the required query from the browse list in the Execute Query dialog box.

When you select a query in the browse list, the query's descriptive name and file name are displayed in the Query Description and File Name boxes at the top of the dialog box.

Select the OK button or press the Enter key to run the query otherwise select another query or select the Cancel button or press the Esc key.

While the query is running the Status Bar will provide you with a status of your query.

Further Information

Ý 3. Create and Run Queries


3.14 Save a Query

By saving the query, you can run it again at another time or use it as the basis for another query. All LANSA Client queries are saved in the LANSA Client folder.

To save a query in the Query window:

  • Select the Save or the Save As command from the File menu or use the [image] from the toolbar.

The Save As dialog box is displayed.

File Name

Enter the file name for the query. This same file name should be used if you create a report format to use with the query.

This file name must be unique. If the name is not unique, LANSA Client will ask if you want to override the existing query.

All LANSA Client query files have an LCQ extension. LANSA Client will add it for you. Any other file extension will be rejected.

Press Save to save the query. To cancel the saving of the query select the Cancel button.

Further Information

Ý 3. Create and Run Queries


3.15 Cancel a Query

To cancel a query which is running, select the Cancel Query command from the Query menu.

It may take some time before the query is actually cancelled. This will depend on the Router you are using on your workstation.

Further Information

Ý 3. Create and Run Queries


3.16 Delete a Query

To delete a query you can:

  • select it in the Open or Save As dialog boxes (both from the File menu) and press the Delete key on your keyboard
  • locate the query in Windows Explorer and press the delete key. LANSA queries have a file extension of LCQ.

Further Information

Ý 3. Create and Run Queries


4. Work with Query Results

If a query's output is not directed to a file or report via the Output To command, the data retrieved will be displayed in a Results window.

Displaying the results in this window gives you the opportunity to review and re-organize your data before saving it to a file, printing it or using it in a chart or report.

Also, from the Results window, you can:

  • execute linked queries.
  • use LANSA Client's dynamic editing features to add, change or delete the data.

Further Information

4.1 About the Results Window

4.2 Enhance Your Results

4.3 Date Time formatting

4.4 Collapse and Expand Your Results

4.5 Copy from the Results Window

4.6 Chart Results

4.7 Save the Results

4.8 Convert the Results to a Report

4.9 Print the Results

4.10 Execute a Linked Query

4.11 Edit/Insert/Delete Facilities


4.1 About the Results Window

The Results window provides a spreadsheet-like representation of the data retrieved after running a query.

The columns in the window match the fields selected for the query. The descriptive names of the fields are used as the column headings.

Each row in the window represents a row of data which has matched the selection conditions specified in the query. These rows are numbered sequentially, starting from number 1.

If summary totals have been specified for the query, you will see additional rows, one for each type of total. These rows are in a different color to the data rows.

Re-sizing Column and Row Widths

You can resize the width of each column or the height of each row using the standard Windows re-sizing techniques. That is, grab the column heading border with your mouse and slide to the left or right as required.

The Result Window adopts the name of the query as its title. If you minimize this window, it will be minimized to an icon with the name of the query as its title. This will help you to identify your Result windows if you have more than one window active at the same time.

Pop-Up Menu

When you are working with the Result window, you can use the pop-up menu to quickly see the LANSA Client commands that are available to you.

To open the pop-up menu, click on the right button of your mouse. You can select any command in the pop-up menu.

Field/Column Level Help (Meta Data)

You can obtain information (meta data) about the fields/columns in your Result window. This information may be extremely helpful if you want to know, for example, the exact meaning of a field, the formula used to calculate it or who is responsible for maintaining it.

To obtain this Field/Column Level Help, select a cell within the column for which you wish to obtain the help then hold down the Shift key and press F1 key at the same time or click on the [image] help icon.

LANSA Client will retrieve the related Field/Column Level Help text from the LANSA Repository on the host and display the text in a window.

Further Information

Ý 4. Work with Query Results


4.2 Enhance Your Results

After you have executed your query you can sort, group and summarize the data using the commands in the Options menu.

This facility gives you the flexibility of varying the data's presentation once you have seen the results - without having to change the query or to rerun it.

  • To re-sort your data or change your sorting requirements, select the Sort Options command.
  • To group or re-group your data, use the Group By command. For example, you may want to change your employee within department grouping to employee within section within department groups.
  • To define or re-define your totaling and summarizing, select the Summary Options command. For example, you may choose to include the average salary as well as the total salaries for each department.

When you specify totals for the query, an additional row for each total will appear at the end of each group as well as a grand total at the end of the results.

Each summary row is identified by a label instead of a row number. If the:

  • Minimum option has been specified, the row label will be Min.
  • Maximum option has been specified, the row label will be Max.
  • Sum option has been specified, the row label will be Sum.
  • Average option has been specified, the row label will be Avg.
  • Count option has been specified, the number of records will be displayed in a row labeled Count.

If the data in a column cannot be summarized (for example, if the column is alphanumeric) the total for that column will be blank.

For details about the Sort Options, Group By and Summary Options commands, refer to Creating and Running Queries.

Further Information

4.2.1 Use Edit Codes to Format your Output

Ý 4. Work with Query Results


4.2.1 Use Edit Codes to Format your Output

When fields are defined in the LANSA Repository, an edit code can be assigned in order to specify the format of the data when it is displayed. The edit codes used in the Repository are the same edit codes used by LANSA Client.

If your output is not in the format expected, a change to the edit code may be all that is required to fix it. You can see the edit code that has been assigned by pressing F1 when you have highlighted the field in the Query Window.

Here is how an amount of 5987.63 would be displayed with the edit codes currently supported by LANSA Client:

Result | Edit Code | Commas? | Decimal Points? | Negative Sign?
5,987.63 | 1 and 2 | Yes | Yes | No
5987.63 | 3 and 4 | No | Yes | No
5,987.63CR | A and B | Yes | Yes | CR
5987.63CR | C and D | No | Yes | CR
5,987.63- | J, K, N and O | Yes | Yes | -
5987.63- | L, M, P and Q | No | Yes | -

Further Information

Ý 4.2 Enhance Your Results


4.3 Date Time formatting

If you save your Results to an Excel, dBase or MS Access file, and if your data contains a date or time, you can specify whether you want the dates or times saved as text/numeric or in a date or time format.

The format that is to be used can be set as a default by the LANSA Client Administrator as described in Create a LANSA Client Environment in the LANSA Client Administrator Guide or it can be overridden using the Date Time Formats command on the Format menu.

Note: The setting required must be specified before the query is executed.

Further Information

Ý 4. Work with Query Results


4.4 Collapse and Expand Your Results

By using the Collapse Group and Expand Group commands in the Results menu on the menu bar, you are able to tailor the view of your data in the Results window.

The Collapse Group command behaves in the exactly the opposite way to the Expand Group command.

Note: You can only use these commands after you have set the grouping you require in the Group By option.

Collapse Group Command

This command allows you to hide the query's details by one level after another, until only the grand total is displayed.

Expand Group Command

This command allows you to display the rows that are hidden in your query by one level after another, until all the details (i.e.rows) are displayed.

Further Information

Ý 4. Work with Query Results


4.5 Copy from the Results Window

You can copy data from the Results window to the System Clipboard.

You can then paste the data from the Clipboard into any other application which has cut-and-paste support.

The data you have copied to the Clipboard will be retained until it is overwritten by the next copy operation.

Copying a Region

Select the region to be copied by pressing the left mouse button and dragging the mouse pointer over the region of interest whilst keeping the button pressed down. Select the Edit menu on the menu bar and choose the Copy command.

Copying a Column

To select a whole column to copy, position your mouse pointer on the column heading and click once with your left mouse button. This will highlight the whole column. You can select multiple columns by dragging your mouse pointer over the required column headings. When you have highlighted all the columns you want to copy to the clipboard, select the Copy command from the Edit menu on the menu bar.

Copying a Row

Select a whole row by positioning your mouse pointer on the row number and click once with your left mouse button. This will highlight the whole row. Select multiple rows by dragging your mouse pointer over the required row numbers. You can then copy the highlighted row(s) to the Clipboard using the Copy command.

Note: With LANSA Client you can copy data to the clipboard but you cannot use the Cut or Paste operations to insert data from another application or query.

Further Information

Ý 4. Work with Query Results


4.6 Chart Results

It is easy to chart the results of your query. All you need to do is:

1.Select some or all of your data in the Results window.

2.Select the Results menu and choose the Chart command or use the [image] chart icon on the toolbar.

A new Chart window will be displayed with the selected data already charted.

You can re-chart another part of your Results without re-running the query by simply re-selecting a new region of data in the Results window.

Note that only numeric data can be charted. If you select alphanumeric data, it will be ignored.

You can simultaneously chart the same data in up to 4 different chart windows. Each window can display a different type of chart. To open a new Chart window, select the New Window command while you are focused on a Chart window. Use the Tile command in the Window menu to see all your charts at once.

How to customize a chart is described in detail in Creating Charts.

Tip

The data you select for a chart must be contiguous. This means that if you wanted to chart columns 1 and 3, your selection of data would automatically include column 2. To temporarily bypass column 2, simply convert it to "Do not Display" by turning off the tick in the Display field in Results box, in the Sort Options dialog box. When you return to the Results window your original column 3 data will be displayed next to column 1. There is no need to re-run your query.

Further Information

Ý 4. Work with Query Results


4.7 Save the Results

You can save the results displayed in the Results window in a variety of file formats. By doing this, you can extract data from your database to update a database on your PC. Most PC applications support the Database Format (DBF) file.

Results must be saved before they can be displayed using a report.

To save the results, from the Results window, select the File command from the menu bar and choose either

  • the Save command, to save new results.
  • the Save As command to save the results to another file.

The Results Save As dialog box is displayed.

Save as Type

Select the format in which you want to save your data. The formats available include:

MS Access Format | .MDB
Excel Format | .XLS
Excel 2003 XML Format | .XML
HTML Format | .HTM
XML Format | .XML
ASCII Text Fixed | .TXT
ASCII Tab Delimited | .TXT
ASCII Comma Delimited | .TXT
dBASE Format | .DBF
dBbase for SCR format | .DBF

Note 1: To save the results for input to a LANSA Client report, you can either choose the MS Access, dBASE or dBASE for SCR format. If you are likely to link this Query with another Query in Crystal Reports then you must choose the MS Access (MDB) or dBASE for SCR format (DBF).

If you are saving a file in Excel 2003 XML Format, HTML Format, XML Format, ASCII Text Fixed, ASCII Tab Delimited or ASCII Comma Delimited the file will be saved using UTF-8 encoding if the results contain any Unicode fields.

File Name

Enter a name for the file. This name should be the same as the query used to create it. It should also be the same as the matching Report Format file, if the results are to be printed as a report.

The file name's length can vary depending on the file type:

DBF | Normal MS Windows file names. For example Report Table File.DBF
XLS | Maximum of 60 characters. This name must not start with a number or include spaces.
TXT | Uses normal MS Windows file names.

Save in

You can choose the location for the file by selecting from the folders listed. By default, the file will be saved in the LANSA Client folder.

Saving the File Details

Once you are satisfied with the entries in this dialog box, select the Save button or press the Enter key.

You can cancel the save by selecting the Cancel button or pressing the Esc key.

Note:

  • Fields marked "Not Displayed" using the Sort Options command will not be saved with the rest of the data.
  • Fields will be written to the file in the sequence selected for the query (as displayed in the Selected Fields area of the Query window).
  • Grouping conditions and totals, if specified, will not be saved with the data.

Further Information

Ý 4. Work with Query Results


4.8 Convert the Results to a Report

To create reports for your queries you can use Crystal Reports. Crystal Reports is a powerful What-You-See-Is-What-You-Get (WYSIWYG) reporting tool allowing you to design sophisticated reports quickly and easily using point-and-click commands. Crystal Reports is used for the report example shown in this section.

Crystal Reports has its own user guide and tutorials.

Once you have created a report, you can save it for future use.

To create or attach a Report to your results, from the Results menu on the menu bar, select:

  • Report Designer, to create a new report or amend an existing report.
  • Report, to display the results using an existing report format.

Remember that you should save the query's results as a DBF/MDB file before requesting a report.

Tip

If you have a report which contains a large amount of data, you may wish to save the report together with the data. To do this, tick the Save data with Report option in Crystal Report's File menu. So that you do not overwrite the data (using the Refresh option) the next time you run the report, you should copy this report file to another name. To include new data next time you run the report, either deselect (i.e. untick) the Save data with Report option, or select the Refresh option when you run the report.

Further Information

4.8.1 Create a New Report

4.8.2 Attach an Existing Report to a Query

Ý 4. Work with Query Results


4.8.1 Create a New Report

To start Crystal Reports, select the Report Designer command from the Results menu on the menu bar.

Refer to the Crystal Reports manual for how to use the Report Writer.

When you are satisfied with the report you have created, you should save it for future use. Reports should be saved to a RPT (Report Format) file with the same File Name as the query. This should also be the file name used to save the results.

Once you have defined your report, you may wish to bypass the Results window and have your results displayed immediately as a report. To do this, simply specify "Report" as the output option for the query, using the Output To command in the Query menu. In future, the results will be automatically displayed in this report layout.

If you wish to link a query to a report with a different file name, you can specify the link using the Linked Query Option command in the Query menu. This facility is useful when the same report layout is used for more than one query.

Further Information

Ý 4.8 Convert the Results to a Report


4.8.2 Attach an Existing Report to a Query

To display your results using an existing report format, select the Results menu from the menu bar and choose the Report command.

If you have not yet saved the results of your query, LANSA Client will ask you to confirm that you wish to proceed.

If there is a predefined report format file with a name matching the file name of your results, your results will be immediately displayed in the report layout on your screen.

If there is no predefined report with a matching file name, LANSA Client will invoke the Report Writer so that you can create a new one.

Note: LANSA Client locates the required report by finding a report format file with the same name as the query's file name.

For example,

a query called: | QUERY1.LCQ
would have a result file called: | QUERY1.DBF or QUERY1.MDB
and a report format file called: | QUERY1.RPT

If it is not possible to use the same name for the query and the report format, you can link them using the Linked Query Option command in the Query menu.

If you specify that the results of a query are to be displayed as a "report" using the Output To command, the results will be immediately displayed in the required report layout, bypassing the Results window. In this case, the results are automatically saved in a file created using the query's file name with a DBF/MDB file type depends on the default DB used.

Further Information

Ý 4.8 Convert the Results to a Report


4.9 Print the Results

You can print the results displayed in the Results window by selecting the File command from the menu bar and choosing the Print command. If you decide to print the results from the Results window, the data will be printed in the raw format.

If you would prefer to print the results in a Report Format, refer to 4.8 Convert the Results to a Report

You can set up or change your printer using the Printer Setup command from the File menu on the menu bar.

Further Information

Ý 4. Work with Query Results


4.10 Execute a Linked Query

Linked queries give you the ability to drill down into your data using the results of one query to act as the search conditions for a subsequent query.

For example, imagine that you wanted to view the sales by Country and check particularly high or low sales as you find them.

Using linked queries, you simply create a query showing you the sales for each country. This becomes your primary query. Then you create another query which shows you the Sales History and link it, as a secondary query, to your first query. You could also create another linked query to show you the Warehouses within each Country.

To use the linked query:

  • Run the primary query.
  • From the result in the Results window, highlight a row that you would like to interrogate.
  • Run the linked or secondary query by selecting the Linked Queries command from the Results menu. From the sub-menu, select the query to be run by clicking on the name with your left mouse button.

LANSA Client will automatically handle all the relevant file linkages and will display the results of the new query in a new Results Window.

  • You can then return to the primary query and interrogate another Country's sales in the same way.

If no linked queries have been defined, you will not be able to select the Linked Queries command.

The names displayed for the linked queries in the drop down list are the names assigned to the queries via the Linked Query Option dialog box. If the linked queries have not been assigned names, then the descriptive name of the file will be used instead.

Refer to LCL006 - Build, Execute and Save a Linked Query in the LANSA Client Tutorials for step by step details of creating linked queries.

Further Information

Ý 4. Work with Query Results


4.11 Edit/Insert/Delete Facilities

LANSA Client provides an editing facility which is extremely cost effective because:

  • the file definitions, all the data validations, business rules and the associated error messages are defined just once in the LANSA Repository.
  • there are no validations or instructions to be recorded with LANSA Client on each PC.
  • LANSA Client automatically generates an entry window to match the data to be edited. This gives you complete data entry screens with absolutely no programming effort required.

You can edit an existing row of data, insert a new row of data or delete a row of data from the Results window.

This editing facility can be put to many different uses. Some of these are:

  • to create and amend test data.
  • to create mini-applications very quickly, perhaps to record the results of a customer survey.
  • to allow employees to update their own employment records. This could be limited to a specific period, perhaps twice a year.
  • to allow managers to record appraisal results. If this only occurs periodically, the cost of developing a fully fledged system may not be justified.

Updating data on the host is very safe using LANSA Client's editing facility:

  • because all the business rules and triggers are defined in the LANSA Repository. LANSA Client does not allow anyone to make changes which violate these rules.
  • if errors are found in the data, it will be rejected and the appropriate error messages will be displayed from the LANSA Repository.
  • before data is accepted, the LANSA Repository determines if the user has the appropriate authority to edit the data.

The editing facility cannot be used when:

  • grouping conditions have been applied to the results.
  • the query includes more than one file.
  • the Allow Inserts/Updates option has not been enabled using the LANSA Client Administrator.
  • editing has not been authorized using the Output Options command.

Note:The Edit Row dialog will only allow you to edit up to 32767 characters per field. If a field is longer than 32767 characters, the entry box for the field will be set to read only.

Note: When you wish to, set SQLNULL value to an RDMLX field with an ASQN input attribute. You must type '*SQLNULL as the field value.

Further Information

4.11.1 Edit Data

4.11.2 Insert Data

4.11.3 Delete Data

Ý 4. Work with Query Results


4.11.1 Edit Data

To change existing data, in the Results window, highlight a row of data to be changed and select the Edit Row command from the Results menu on the menu bar.

An Edit Row dialog box matching the row you have selected will be presented to you with:

  • Key fields displayed in read-only mode as they cannot be changed.
  • The number of the row in the Title bar.

If there are too many fields to be displayed at once in the window, LANSA Client will provide a vertical scroll bar on the right hand side of the dialog box so that you can scroll down or up through the fields.

Change the data as required.

A "..." button will open a DateTime Selection dialog, containing a calendar and fields for entering time values. You can use this as an alternative to typing in lengthy date/time values by hand. Refer to DateTime Selection for more information.

Using the Previous or Next buttons in the dialog box, you can cycle through the rows of data and edit them without returning to the Results window. LANSA Client will cycle from the top-most row to the bottom row as appropriate.

Once you are satisfied with your changes, select the Update button.

LANSA Client will then update the relevant file in the host database. If any errors are found, error messages will be displayed and the update will not be successful. Amend the data according to the errors reported and select the Update button again.

Once you have finished editing the data, select the Close button to dismiss the Edit Row dialog box.

Note:
You can obtain context sensitive help from the LANSA Repository by pressing the Shift and F1 key combination while the mouse cursor is pointing at the field for which help is required. The help text will be displayed in a help window. This help will be retrieved in the language you have defined for your LANSA Client environment or specified using the Set Language command in the Options menu.

Note: The Edit Row dialog will only allow you to edit up to 32767 characters. If a field is longer than 32767 characters, the entry box for the field will be set to read only.

Further Information

Ý 4.11 Edit/Insert/Delete Facilities


DateTime Selection

This dialog box appears when you click a "..." button in the Field Conditions, Conditions Prompt or Edit/Insert Row dialogs.

It is used to enter the components of a Date or Date Time value without having to worry about getting the format right.

Use the arrows to select month and year - alternately clicking on the month gives you a pulldown list, and clicking on the year lets you enter the year you want.

Click on a date in the calendar section to select the day. In the Hour, Minutes and Seconds sections enter the desired values. Note that the minimum value on all three entries is zero, the maximum for Hour is 23 and the maximum for Minutes and Seconds is 59.

The Hour, Minutes and Seconds will be greyed out if the value you're creating is a Date only.

When you press OK, this dialog box will close, the value you have selected will be formatted and will appear on the parent dialog.


4.11.2 Insert Data

To insert a new record or row of data into the file displayed in the Results window, select the Results menu from the menu bar and choose the Insert Row command. A dialog box will be presented to you to insert the new data.

Note: When you are inserting new records, you should make sure that your query selects all the fields in the file. If you do not include all fields, the insert may fail because fields are missing in the new record.

The entry fields in this dialog box are based on the columns in your current Results window. Each entry field will initially contain the default value defined for the field in the LANSA Repository.

If there are too many fields to be displayed at once on your screen, you will find a vertical scroll bar on the right hand side of the dialog box. Use this to scroll up and down through the fields.

When you have entered the details you require, select the Insert button or press the Enter key. LANSA Client will attempt to insert the new record into the selected file. The LANSA Repository will determine if the new data contains any errors and if you are authorized to insert new records into the selected file.

You can insert as many records as you wish without having to return to the Results window. Once you have finished inserting the new records, select the Close button to dismiss the dialog box.

By selecting the Shift and F1 key combination, you can obtain help about the fields you are creating from the LANSA Repository.

Further Information

Ý 4.11 Edit/Insert/Delete Facilities


4.11.3 Delete Data

To delete a row of data (i.e. a record), highlight the row to be deleted in the Result window and select the Delete Row command from the Results menu on the menu bar.

You will be asked to confirm that you wish to delete the selected record. The LANSA Repository will determine if you have the authority to delete records from this file.

Note: This command is only available when you select only one record from the Results window. It is not available if more than one row of data is selected.

Further Information

Ý 4.11 Edit/Insert/Delete Facilities


5. Create Charts

The results of a query are charted in the Chart window.

In the Chart window, you can:

  • extensively customize a chart to suit your immediate requirements.
  • display multiple Charts side by side, with each chart in a different chart style.
  • locate the actual data which made up a selected portion of the chart.
  • copy the chart into another application such as a letter or report, or save it as a file for future use.

Further Information

5.1 Create a New Chart

5.2 Select the Chart Type

5.3 Customize a Chart

5.4 Multiple Chart Windows

5.5 Interactive Hot Charts

5.6 Special Effects with Pie Charts

5.7 Copy a Chart into Another Application

5.8 Print a Chart

5.9 Save a Chart


5.1 Create a New Chart

You create the first chart for a query from the Results window. To do this:

  • First select the data to be charted, by:
  • pressing your left mouse button when the mouse pointer is over the first "cell" of data to be charted
  • dragging the pointer to the last "cell" you want to chart while keeping the mouse button depressed
  • releasing the mouse button.
  • Select the Chart command from the Results menu on the menu bar.

This will instantly display a chart of the selected data. The type of chart and the options such as color range, chart name's font and so on, will be displayed in whatever default has been specified in the Output Options. If there are no defaults specified, then your data will be charted using a vertical bar chart.

Tip

If you want to chart only the totals of your query, just hide the data rows using the Collapse Group command from the Results menu, then you can select just the summary rows you want to include in a chart.

You can now:

  • choose another chart type as described in 5.2 Select the Chart Type.
  • change the look of the chart as described in 5.3 Customize a Chart.
  • insert the chart into another document as described in 5.7 Copy a Chart into Another Application.
  • save the chart, as described in 5.9 Save a Chart.
  • open another Chart window as described in 5.4 Multiple Chart Windows .
  • select another portion of your Results to chart.

Further Information

Ý 5. Create Charts


5.2 Select the Chart Type

For each Chart window, you can specify the type of chart you want from the drop-down list displayed when you select the Chart menu on the menu bar. Alternatively, you can choose a chart type by selecting it from the Chart Options dialog box.

You can chart your data using a:

  • 2-Dimensional Pie Chart
  • 3-Dimensional Pie Chart
  • 2-Dimensional Bar Chart
  • 3-Dimensional Bar Chart
  • Line Chart
  • Log Linear chart
  • 3-Dimensional Area Chart
  • 2-Dimensional Area Chart
  • Scatter Plot Chart
  • Polar Chart
  • Tape Chart

Further Information

Ý 5. Create Charts


5.3 Customize a Chart

You customize your chart using the options in the Options... dialog box that you select from the Chart menu.

Tip

To reduce the amount of customizing you need to do, you should specify the defaults that you would like used for a new chart. To do this, select the Output Options command in the Query menu. The defaults you can set are identical to the options described here.

To adjust the current chart, from a Chart window, select the Options command from the Chart menu on the menu bar.

In the right hand corner of the dialog box is a sample of the chart style you have chosen. As you select the options you require, they are immediately reflected in the sample.

Chart Title

You should give your chart a meaningful title.

Left Title & Bottom Title

Specify titles for the left and bottom margins of the chart if you want to, by entering the text you require in the Left title and Bottom title entry boxes respectively.

Chart Type

You can change the chart type by selecting the required type from the drop down list. You can choose a:

  • 2-Dimensional Pie Chart
  • 3-Dimensional Pie Chart
  • 2-Dimensional Bar Chart
  • 3-Dimensional Bar Chart
  • Line Chart
  • Log Linear chart
  • 2-Dimensional Area Chart
  • Scatter Plot Chart
  • Polar Chart
  • Tape Chart
  • 3-Dimensional Area Chart

Tip

You can also change your Chart Type by selecting the Chart Type you require from the Chart menu on the menu bar.

Hot Chart

Enable or disable the Hot Charting facility by selecting or deselecting this Hot Chart button. Refer to 5.5 Interactive Hot Charts for details of Hot Charting.

Chart Style

The styles available are dependent on the chart type you have selected. For example, if you have chosen a bar chart, you could choose a horizontal, vertical and stacked style or for a pie chart, you could choose to include colored labels or % labels and so on.

Grid Style

This option allows you to choose the type of grid, if any, to appear at the back of your chart.

Labels

This list box lets you choose whether you want labels on your chart and if so, where they are to be.

Legend Column

From the fields in your query, select those you want to print in the legend. You can also choose not to include a legend.

Legend Style

Use this option to customize the appearance of the text used in the legend, for example to use italics.

Color Palette

This option allows you to change the type of color used for your chart, such as from full color to pastels.

Draw Style

Use this option to change the drawing style of your chart, for instance from color to black and white.

Y-axis Style

You can change the scale and range of the Y-axis in a chart using this list box. If you choose User-defined from the list, the maximum Y-value in the chart will be the maximum value found in your selected data.

Line Statistics

If you have selected a Line chart type, you can add one or more statistics to it by choosing Mean, Standard, Deviation or Best Fit.

Foreground

From this list box you can choose the font color for all the text on your chart.

Background

Select the background color for your chart window.

Font Use & Fonts Style

You can choose the font style for each type of text in your chart. For example, you could bold your Chart Title. Click on the down arrow of the Font Use box for the list of texts you can change, then use the Font Style option to define the style of font you want to use.

Print style

If you have a color printer, you can use this option to print in color or monochrome.

Saving your Chart Options

Once you are satisfied with the options you have specified, select the OK button or press the <Enter> key. The current Chart window will immediately reflect your changes.

If you want to discard the options you have specified, select the Cancel button or press the <Esc> key. To return to the prior settings, select the Reset button.

Further Information

Ý 5. Create Charts


5.4 Multiple Chart Windows

You can chart a single set of data using up to four different chart types at the same time.

Request each additional Chart window by selecting the New Window command from the Window menu.

Select the Tile command from the Window menu so that you can see all your charts at the same time.

You can resize any of the windows if you want to.

Having more than one Chart window in view at the same time helps you to choose the chart that most effectively displays your data.

Further Information

Ý 5. Create Charts


5.5 Interactive Hot Charts

The interactive Hot Chart facility allows you to immediately locate from your chart the section of the results that makes up a segment of the chart.

To do this, simply click with your left mouse button on any portion of a chart and the associated row of data in your results window will be instantly highlighted.

This means, for example, that if you notice that there is an abnormality in your chart, you will be able to immediately pinpoint that section of data in your results. If you are charting a Linked Query, you can then 'drill down' further into your data to determine the cause of the abnormality. This is a powerful feature of LANSA Client.

The interactive Hot Chart facility is more effective if you use the Tile command from the Windows menu to display the Results and Chart windows at the same time.

Note: Once a row of data is highlighted, do not select that data again in the results window as this will cause the chart to be recreated using just the selected portion of data.

To turn this facility off, click on the Hot Chart box in the Chart Options.

Further Information

Ý 5. Create Charts


5.6 Special Effects with Pie Charts

You can explode or "lift out" a portion of a pie chart by pressing the <Shift> key while clicking with your mouse pointer on the portion of the chart you want to explode. Repeat the same action to toggle between exploding and not exploding portions of the chart.

To explode all the portions of the pie chart, press the <Ctrl> and <Shift> keys simultaneously and, while keeping these keys pressed, click on any portion of the chart. This action will explode all the portions of a pie chart. Repeat the same action to toggle between exploding and not exploding the pie chart.

Further Information

Ý 5. Create Charts


5.7 Copy a Chart into Another Application

There are two methods you can use to insert a chart into another application.

These are:

  • Copying the chart to the Windows Clipboard and then pasting it immediately into the other application.
  • Saving the chart as a file and inserting it into another application when you choose.

If you are including the chart in a report generated by the LANSA Client Report Writer, you must save it as a file.

Note that you can only copy or save a whole chart. You cannot copy or save a portion of the chart.

Using the Windows Clipboard

1. From the window you wish to copy, select the Copy command from the Edit menu on the menu bar.

2. Paste the chart from the Clipboard into the other application.

Saving the Chart as a file

1. From the window you wish to save, select the Save As command from the File menu.

2. Specify either a File type of bitmap (BMP) or metafile (WMF), the file name and the location for the file, as requested in the Chart Save As dialog box.

3. Copy the chart file into the other application using the Picture command in the Insert menu.

Further Information

Ý 5. Create Charts


5.8 Print a Chart

You can print the chart displayed in the current Chart window by selecting the Print command from the File menu on the menu bar.

If you have a color printer, you can switch the print style between monochrome and color using the Print style option in the Chart Options dialog box.

You can change your printer via the Printer Setup command if you have more than one printer attached to your workstation.

Further Information

Ý 5. Create Charts


5.9 Save a Chart

By saving a chart, you can incorporate it into another application at a later stage.

To save the chart displayed in the chart window, choose either the Save or Save As commands from the File menu on the menu bar. The relevant Chart Save or Chart Save As dialog box appears.

The Chart Save/Save As dialog boxes are standard Windows dialog boxes where you specify the file name, the file type and the location for the file.

A chart can be saved either as a bitmap (BMP) or a metafile (WMF) formatted file.

Select the OK button or press the <Enter> key to save the chart.

To cancel the saving of the chart, select the Cancel button or press the <Esc> key.

Note: When you save a chart, you are saving the current chart with the headings and colors, etc. that you have selected. The options you have chosen with this chart are not saved as defaults to be used when the query is next run.

Further Information

Ý 5. Create Charts


6. Generate Applications

LANSA Client allows you to generate stand-alone applications. These are particularly useful

  • for queries that you run frequently

and

  • if you need to develop an application quickly.

Generated applications can include any number of LANSA Client queries, giving you the ability to retrieve, update, insert and delete data without first starting LANSA Client.

To start a generated application, you simply click on an icon outside of the LANSA Client environment.

To create a generated application, you do not need to write ANY program code. You just point and click with your mouse.

Generated applications are easily distributed to any number of workstations, however the workstation must have LANSA Client installed. You may need to purchase extra LANSA Client licenses before you can distribute your generated applications.

Note: If you are upgrading from an earlier version of LANSA Client, please refer to the notes in 6.5 Application Maintenance .

Refer to LCL007 - Generate an Application

Further Information

6.1 Overview

6.2 Design Your Application

6.3 The Menu Bar

6.4 Run the Generated Application

6.5 Application Maintenance


6.1 Overview

When you generate a new application, all you need to do is:

1. Give the application a name.

2. Assign a name to the file which will hold the application's definition.

3. Choose the icon to represent the application.

4. Select the queries to be included in the application and create commands so that they can be selected for running.

5. Specify the host from which the information will be retrieved, if there is more than one.

6. Press the Generate button when you are finished. This will generate the application.

Each of these steps is described in detail in 6.2.1 Generate Application Dialog Box.

Before you generate an application you must:

1. Construct one or more queries to use in the application.

2. Test the queries, to make sure they execute correctly.

3. Save the queries.

4. Have the application generation facility enabled in your LANSA Client environment. The LANSA Client environment is described in Administrator Tasks.

Further Information

Ý 6. Generate Applications


6.2 Design Your Application

When you are ready to generate an application, or if you want to amend an existing application, select the Generate command from the File menu on the menu bar. The Generated Applications dialog box is displayed with a list of existing applications, if any.

To generate a new application:

  • Select the New button.
    A Generate Application dialog box is displayed with a list of the standard menu items, such as File, Edit, Options and so on, ready for you to use.

To amend an existing application:

  • Select the application you wish to change from the list of existing applications in the Generated Application dialog box and press the Change button.
    The Generate Application dialog box is displayed with the details of the application you have selected.

To dismiss the Generated Applications dialog box:

  • Press the Cancel button.

Further Information

6.2.1 Generate Application Dialog Box

Ý 6. Generate Applications


6.2.1 Generate Application Dialog Box

You use the Generate Application dialog box to specify all your application's requirements, both for new applications or when changing existing applications.

In the Generate Application Dialog Box you will:

Step 1. Give the Application a Name

In the Application Name box, enter the name or description of the application. It is best to use a description that reflects the purpose of the application.

LANSA Client does not check if this name is already in use.

Step 2. Assign a Name to the File

In the Executable Name box, enter the name of the file that will contain the definition of the application. This is a standard Windows file name and it must be unique. If you use an existing name, you will be asked if you wish to overwrite the existing file.

When your application is generated, an extension of MNU will be added to this name.

Step 3. Choose an Icon

In the top right corner of the dialog box is the icon that you will use to start the application. The LANSA Client icon is initially displayed. If you would prefer to use a different icon, select the Change button to browse through your files to choose another icon.

The new icon is immediately displayed in the top right corner of the Generate Application dialog box.

Step 4. Select the Queries

Select the queries to be included in your application from those listed in the Query to Execute drop down list or use the Browse button to locate the query. In order to execute your queries when you run your application, you need to add a command to your application's menu bar. How to do this is described in 6.3.1 Add to the Menu Bar. In 6.3 The Menu Bar section you will find a list of all the menu bar items and the commands that are supplied ready for you to use.

Step 5. Assign a Host System

You can assign a specific host system to be used with your application by selecting the Attach Host System option. From the Host System listbox, select the host system you wish to connect to when running this application.

If you don't specify a host system at this stage, and if more than one host system has been specified for the LANSA Client environment, the Select Host dialog box will be presented to allow the host system to be selected each time the application is started.

Step 6. Generate the Application

When you are satisfied with the definition of your application, press the Generate button to generate the application.

To discard the definition, select the Cancel button.

When LANSA Client has generated the application, it will create a file with an MNU extension and the name you specified as the Executable Name. The MNU file, plus a copy of all the queries included in the application are stored in the APPS folder which is a sub-folder of the LANSA Client folder.

The generated application will be added to the LANSA Client Windows Group with the icon and the application name you have selected.

If any changes are needed to the application, return to the beginning of these steps to make them. You cannot change the MNU file directly.

Further Information

Ý 6.2 Design Your Application


6.3 The Menu Bar

To help you quickly create your application, LANSA Client provides you with a ready-to-use menu bar containing a standard set of menus and commands. You can add your own commands to these standard commands but you cannot change or delete any of the standard commands.

The ready to use items on the menu bar are listed in the Menu box. These are:

  • File
  • Edit
  • Options
  • Results
  • Chart
  • Window
  • Help

Ellipses (...) after an item in the Menu box indicates that commands are attached to the menu item.

The commands supplied for File are:

  • Save
  • Close
  • Print
  • Print Setup
  • Disconnect
  • Exit

The command supplied for Edit is:

  • Copy

The command supplied for Options is:

  • View

The commands supplied for Results are

  • Chart
  • Linked Queries
  • Edit Row
  • Insert Row
  • Delete Row
  • Report

The commands supplied for Chart are:

  • Options
  • Chart types

The commands supplied for Window are:

  • New Window
  • Tile
  • Cascade
  • Freeze
  • Arrange Icons

The commands supplied for Help are:

  • Contents
  • Using Help
  • About

These commands operate in the same way as the commands described in Commands.

Further Information

6.3.1 Add to the Menu Bar

6.3.2 Add a New Menu

6.3.3 Select a Query and Add Commands

6.3.4 Change a Command

6.3.5 Delete a Command

6.3.6 Re-organize the Commands on the Menu Bar

Ý 6. Generate Applications


6.3.1 Add to the Menu Bar

For each query you include in your application, you must specify a command to be used to start the query.

You could add these command(s) to one of the menu bar items supplied or you could choose to create a new menu bar item and add your queries to that.

Further Information

Ý 6.3 The Menu Bar


6.3.2 Add a New Menu

1. Decide where you want the new item to appear on the menu bar. A new item will be inserted directly before whatever item you highlight in the Menu list.

2. Press the Add button. A new entry with no name will be inserted into the Menu box list in the position you have selected.

3. Enter the name to appear in the menu bar in up to 30 characters in the Menu Text box.

To create a short cut key for this item, insert an ampersand (&) immediately to the left of the character you would like to use. For example &Queries will cause the keys <Alt> and <Q> to be used as short cut keys for this menu and Queries to be displayed on the menu bar. If you entered, instead, Q&ueries, the shortcut keys would be <Alt> and <U>.

Repeat these steps to add another menu name to the menu bar if you wish, otherwise continue to the next step, which is 6.3.3 Select a Query and Add Commands

Further Information

Ý 6.3 The Menu Bar


6.3.3 Select a Query and Add Commands

1. Choose the position in the list for your sub-command. For example, directly below the Queries menu item.

2. Press the Add button.

3. In the Menu Text box, enter the name you want to appear in the drop down menu list. For example, SimpleQuery.

Add an ampersand to the left of the character to be used as the short cut key. For example, &SimpleQuery will give you SimpleQuery. You must choose a character that is not already in use.

4. Indent the new command by pressing the right facing arrow at the bottom of the Menu box. This will cause the command to be included in a drop down list, in this example from Queries, rather than to be included in the menu bar.

5. Select the query you want to be executed by this command from the Query to Select's drop down list.

Note: The only queries in the Query to Select dropdown list are those that have been saved.

Repeat these steps until you have added a command for each of the queries to be included in your application.

Further Information

Ý 6.3 The Menu Bar


6.3.4 Change a Command

To change a menu command:

1. Select the command to be changed from the list in the Menu box. The corresponding query's details will be immediately displayed in the Menu Text and the Query to Execute boxes.

2. Change the description by specifying the new description in the Menu Text entry box.

3. Replace the query for the command by selecting another query from the Query to Execute list.

4. You cannot change any of the standard commands.

Further Information

Ý 6.3 The Menu Bar


6.3.5 Delete a Command

To delete a command from the Menu list:

1. Select the appropriate command.

2. Select the Delete button. You will be prompted to confirm the deletion.

Reminder: You cannot delete any of the LANSA Client standard commands.

Further Information

Ý 6.3 The Menu Bar


6.3.6 Re-organize the Commands on the Menu Bar

The hierarchy level of the commands determines how they are positioned and displayed in the menu bar of the application. You can see the hierarchy by the way the commands are indented in the Generate Application Menu box.

  • Level 1 is the highest level in the hierarchy of commands.
    Level 1 items are displayed on the menu bar.
    All new commands are initially set at level 1.
  • Level 2 commands are used as the dropdown commands from the menus on the menu bar.
  • Level 3 commands are used as dropdown commands from the level 2 commands.

You can arrange the levels as you wish.

Changing the Command Hierarchy

1. From the list in the Menu box select the command you want to change.

2. To promote the command to a higher level, press the Left Arrow button at the bottom of the Menu list.

3. To demote the command, press the Right Arrow button. The command will be demoted one level.

In general, you would demote a command from level 1 to level 2 or promote a command from level 2 to level 1. You cannot promote a command above level 1.

Re-sequencing the Commands

To re-sequence the commands in your Menu list, select the command to be moved then select the button with the Up Arrow, to move the command up, or the button with the Down Arrow, to move the command down.

Further Information

Ý 6.3 The Menu Bar


6.4 Run the Generated Application

The application definition file, which contains the definitions of your generated applications, will be found in the APPS folder. This is a sub-folder of the LANSA Client folder.

You can run the generated applications:

  • From the Windows Menu.
  • From Explorer by double clicking on the required .MNU file.
  • Typing the file name at a command prompt.

Using the Menu

To start an application, click on the Windows Start button, locate the relevant item in the LANSA Client menu and select it.

Using Explorer

Locate the application file (with extension .MNU) and simply double click on it.

From the Command Prompt

In the Command Prompt window enter:

"<drive>:\Program Files\LANSA Client\LCRUN32.EXE"

"<drive>:\Program Files\LANSA Client\Apps\<queryname>.mnu"

and press <Enter>.

In this example <drive>:\Program Files\LANSA Client represents the drive and directory in which LANSA Client has been installed.

Generated applications are stored in the Apps directory.

Note that when you enter a path with embedded spaces, you must enclose the path with quotes as shown in the example above.

Further Information

Ý 6. Generate Applications


6.5 Application Maintenance

The generated application will always execute the version of the query that was selected at the time the application was generated. If you modify any of the queries used in a generated application, you must make sure that the generated application includes the latest version of all the queries.

To do this, you need to regenerate the application. That is:

1. Select the Generate command from the File menu.

2. Select the required application from the list in the Generated Applications dialog box and then select the Change button.

3. In the Generated Application dialog box, select the Generate button. You don't need to modify the application.

4. You will be asked to confirm that you want to replace the queries associated with the application. Select the Yes button.

Your application will now execute the latest versions of the queries.

Alternatively, you could manually copy the file of the query which has been modified from the LANSA Client folder to the APPS folder. Query files have an LCQ suffix.

Upgrading Applications

Applications generated under older versions of LANSA Client may not run correctly under later versions. If this is the case, you need to:

1. Open each query included in the application, using the Open command.

2. Save the queries using the Save command. This ensures that your query files are saved in a format which can be recognized by later versions of LANSA Client.

3. Regenerate your applications by selecting the Generate command. Be sure to select the Yes button when you are asked if you want to replace the existing query files.

Further Information

Ý 6. Generate Applications


7. Automate Query Execution

LANSA Client allows you to automate some of the actions required to run a query. You do this by executing the query using a command rather than starting LANSA Client and executing the query from the Query window. This facility can be useful if you:

  • run the same query repeatedly
  • want to schedule the execution of a query at certain times
  • want to run a series of queries in batch mode when you are not present.

You use LCRUN32.EXE, installed as part of LANSA Client, to initiate the execution of a query from a command line.

Any variable values required by the query can be included on the command line or in a file called from the command line.

Further Information

7.1 How It Works


7.1 How It Works

You can run a query from a command line entered in either:

  • the Windows Start/Run entry box

or

  • a DOS window

using a command like this:

LCRUN32.EXE <queryname>.lcq

where queryname is the path and name of your query.

When you execute a query in this way, you can choose to run it in batch mode or as a normal query, with dialog boxes prompting you to insert any variables, just as if the query was run from the Query window.

If you choose to run a query in batch mode, you will not be prompted to insert any variable (i.e. promptable) values that may have been defined for the query. If promptable values are required, you can include them in the command or in a file.

Further Information

7.1.1 Run a Query in Batch Mode

7.1.2 Run a Query Interactively

7.1.3 Specify the Host

7.1.4 What if You Have a Prompt?

7.1.5 Control Report Options

7.1.6 Specify Parameters via an Input File

7.1.7 Batch Mode Troubleshooting

Ý 7. Automate Query Execution


7.1.1 Run a Query in Batch Mode

To indicate that you want the query to run in batch mode, you need to add a parameter to the command line to notify LCRUN32.EXE that it is to execute this query in batch mode.

The parameter to include in your command is:

-b

Alternatively, you could use -B or /b or /B.

For example:

LCRUN32.EXE -b d:\MyApplications\Client32\cities.lcq

will run the query cities.lcq in batch mode.

Note: THE BATCH COMMAND PARAMETER MUST ALWAYS BE FOLLOWED BY A SPACE. Your query could be corrupted if you fail to leave a space after the b.

Note: If you schedule your query using a Windows Scheduler service, you MUST specify /INTERACTIVE for the scheduler's AT command. Your Windows NT scheduler Help will provide more information.

Further Information

Ý 7.1 How It Works


7.1.2 Run a Query Interactively

If you choose not to run a query in batch mode, the query will behave as if it had been started via the LANSA Client query window, displaying dialog boxes and indicating progress, and so on.

Further Information

Ý 7.1 How It Works


7.1.3 Specify the Host

If you have more than one host to connect to, you tell LCRUN32.EXE which host to connect to using one of these commands:

HSTN=<n>

or

HSTD=<description>

These commands are the equivalent of the Select Host dialog box.

HSTN=<n> tells LCRUN32.EXE to connect to the nth host in the Select Host list.

HSTN=<description> tells LCRUN32.EXE to connect to the host with the description that matches <description>.

For example, if your Select Host dialog box contained two hosts, Manufacturing and Sales, then your parameter would be:

HSTN=2 if you want to select the second host on the list

or

HSTD=Sales if you wish to select the second host by name.

If you use the host's description in the command parameter, you must make sure that the name you enter is exactly the same as on your Select Host list. You cannot use a name that contains a space. If you need to use a name containing a space, then you must put the parameter into an input file. Alternatively, you could specify the sequence number.

If you specify the host as a sequence number, LCRUN32.EXE will select from the list of hosts in the sequence in which they are currently listed.

If you don't include this parameter and you have more than one host to connect to, the first host will be selected.

Further Information

Ý 7.1 How It Works


7.1.4 What if You Have a Prompt?

If you run a query in batch mode and it has been defined with a Prompt to request information, you can supply the selection criteria as a parameter via either:

  • the command line

or

  • a file as described in 7.1.6 Specify Parameters via an Input File .

The format of the parameter is:

QPnn=<parameter value>

where nn is the sequence number of the parameter and <parameter value> is the actual value to be used. This parameter is the equivalent of the Condition Prompt dialog box.

The prompt parameter which is equivalent to a Conditions Prompt dialog box requesting the Sales Country "AU" would be: QP01=AU

If you do not include this parameter, the query will be completed using the default value.

If your query had more than one promptable value, you could enter the values like this:

LCRUN32.EXE CITY.LCQ /b QP01=AUSTRALIA QP03=10000

This command will run the CITY.LCQ query in batch mode with:

  • the first promptable/parameter value (QP01) set to AUSTRALIA.
  • the second promptable/parameter value (QP02) is not supplied so it would be set to its default.
  • the third promptable/parameter value (QP03) set to 10000.
  • If your promptable value contains a space, then enclose the parameter in quotes like this QP01="SOUTH AFRICA".

Note: If the parameter value contains an embedded quote, such as QP01=O"PSM, then the parameter must be supplied in an input file.

Further Information

Ý 7.1 How It Works


7.1.5 Control Report Options

If you have selected "Report" as the location for your query's output, there are two ways you can ensure that report output goes to the default printer rather than to a report window.

These are:

  • specifying a value of P for 'Output to Report' RPTO parameter with the command.
  • specifying the required output options using the LANSA Client commands when the query is defined.

Using the OUTPUT TO REPORT Parameter

The RPTO parameter is used to control the output of the query if you have selected Report as the location for your query's output.

For example, by including either:

RPTO=W or RPTO=w

you would force all report output to go to a window, even if the query definition specifies the report output is to go to a printer.

By including either:

RPTO=P or RPTO=p or /p

you would force all report output to go to the (default) printer, even if the query definition specifies the report output is to go to a window.

Using the LANSA Client Commands

You do not need to include the RPTO parameter if you specify that the query's output is to be a Report and it is to be printed by the default printer attached to your workstation.

You do this by selecting Report as the Output To option and then specifying that the default printer is to be used in the Output to Report Options dialog box. Refer to Commands for more information.

Further Information

Ý 7.1 How It Works


7.1.6 Specify Parameters via an Input File

If you specify the parameters via an input file, you must include a parameter in the command line to indicate the location of the file.

This parameter's format is:

QFIL=<file path & name>

For example:

LCRUN32.EXE CITY.LCQ QFIL=c:\temp\city.inp

will run the CITY.LCQ query taking parameters from the c:\temp\city.inp file.

The parameters on the input file must follow these rules:

  • One parameter to a line.
  • The same format is used whether the parameter is input via a command line or file.
  • The parameter value is taken to be the text to the end of the line (including any spaces or tabs).
  • Blank lines are ignored.
  • Lines beginning with // are regarded as comment lines.

For example:

// an example input file

// set the first query parameter to NEW ZEALAND

QP01=NEW ZEALAND

// the second query parameter is missing so the default is used

// set the third query parameter to be 1000

QP03=1000

//and connect to the second host

HSTN=2

Note: If your parameter value includes an embedded quote ("), then you must supply the value via an input file rather than a command line.

Further Information

Ý 7.1 How It Works


7.1.7 Batch Mode Troubleshooting

To investigate problems when running queries in batch mode, you can specify an output file with the batch mode option as in this example:

LCRUN32.EXE -bd:\temp\batch.txt <drive>:\MyApplications\Client32\cities.lcq

Note: In this case, the batch parameter (-b) is NOT followed by a space.

This command will tell LCRUN32.EXE to put information messages into the d:\temp\batch.txt file. You can then view this file to get an indication of what happened during the execution of the query.

Note that LCRUN32.EXE always appends to the output file. This means an existing file is not overwritten. It is up to you to maintain the file by deleting/renaming/moving it, as required.

Further Information

Ý 7.1 How It Works


8. Commands

There is more than one way of executing most LANSA Client commands. You can select the command:

  • from the Menu Bar.
  • from the icon in the Tool Bar.
  • using the accelerator keys (also called short-cut keys) associated with the command.

Further Information

8.1 The Menu Bar


8.1 The Menu Bar

The Menu Bar contains all the LANSA Client commands.

The more commonly used commands have also been included as icons on the Tool Bar.

Some of the LANSA Client commands, for example Exit, operate in the same way as the equivalent commands in other Windows software. The majority of the commands, however, are specific to LANSA Client.

Each item on the menu bar leads to a drop down list which contains a series of commands.

These are the items on the menu bar:

  • 8.2 File Menu
  • 8.3 Edit Menu
  • 8.4 Options Menu
  • 8.5 Query Menu
  • 8.6 Results Menu
  • 8.7 Chart Menu
  • 8.8 Window Menu
  • 8.9 Help Menu

Further Information

Ý 8. Commands


8.2 File Menu

The File menu on the menu bar contains commands which allow you to work with the Query, Result or Chart windows. It also contains the command for you to generate an application.

The File menu contains these commands:

  • 8.2.1 New
  • 8.2.2 Open
  • 8.2.3 Run
  • 8.2.4 File Overrides
  • 8.2.5 Save
  • 8.2.6 Save As
  • 8.2.7 Close
  • 8.2.8 Print
  • 8.2.9 Printer Setup
  • 8.2.10 Generate
  • 8.2.11 Disconnect
  • 8.2.12 Exit

Further Information

Ý 8. Commands


8.2.1 New

Use the New command to create a new query.

When you select this command, LANSA Client will open a new Query window. In the Files to Select From area of this new window will be a list of all the files that you are authorized to access from the host system to which you are connected.

Refer to LCL002 - Run a Simple Shipped Query in the Tutorials for details about Queries.

Refer to 8.4.9 Display Object Name, 8.4.10 Display Logical Files (Views), 8.4.11 Display Physical Files for variations that can be applied to the view you see in this window.

To access an existing query, use either the 8.2.2 Open or 8.2.3 Run commands.

Tip

You can have more than one query open at the same time. A different query can be constructed in each window.

Further Information

Ý 8.2 File Menu


8.2.2 Open

The Open command or [image] the Open toolbar icon opens an existing query so that you can check its definition and make any modifications before running it.

When you select this command, you are presented with the Open Query dialog box so that you can select the query you wish to open.

To run a query you have opened using this command, use the 8.5.1 Execute command from the Query menu.

Refer to LCL002 - Run a Simple Shipped Query in the Tutorials for details about Queries.

Further Information

Ý 8.2 File Menu


8.2.3 Run

The Run command, or [image] toolbar icon, allows you to run an existing LANSA Client query immediately, without modifying it.

When you select this command, you are presented with the Execute Query dialog box so that you can select the query you wish to run.

If you wish to modify a query before running it, select the 8.2.2 Open command instead of this command.

Further Information

Ý 8.2 File Menu


8.2.4 File Overrides

The ability to override specific Files and/or Libraries on the server when performing queries is new to LANSA V13. This allows Client to tell the server that when querying particular files, the server is actually to reference data from an alternative table on the server.

The File Override Dialog provides an interface for creating one or more sets of File and Library overrides, and choosing which set to activate. This is typically something an administrator would do, as it requires technical knowledge of database table and owner names on the server.

This is the dialog when first opened, before any data is entered:-

The File Override Sets list has just one entry, No Active File Override. To add your own overrides, you must first create a set. This is done by typing a unique name for the set in the field to the right of the Add… button, then clicking Add…

The new entry is added to the list, and when selected it will be the active set of overrides for all LANSA Client queries, as well as allowing you to add, edit and delete specific file and library overrides.

In the image following, the Example1 set has been selected, then the Add Override button clicked. A new dialog opens providing fields specifying the File and Library to be overridden on the server when queries are performed. Clicking OK adds the new entry to the Active Set Contents list. Note that you do not need to enter values for all four fields; if you merely wish to override a file's table and leave the library unchanged, the Library and Table Owner fields can be left blank. In the same way, you can override a Library with a new Table Owner, leaving the File entries blank – this change would therefore affect all Files that use that Library.

In this way multiple file and library overrides can be added. To Edit or Delete an existing entry in the Active Set Control list, click on the appropriate row in the list and then click the Edit Override or Delete Override button.

To delete an entire set of overrides, choose its entry in the File Override Sets list and click the Delete… button.

The screen capture following shows that two sets have been created, and that the active one contains three overrides:

When you are finished, make sure the set that you want active is selected in the File Override Sets list, then click OK.

Your changes will be written to a file stored in your Client directory (typically C:\Program Files\LANSA Client V12) called fileovrr.lcd. This file can then be copied to the Client directory on other computers as needed.

Further Information

Ý 8.2 File Menu


8.2.5 Save

Use this command or [image] toolbar icon to save a query, the results of a query or a chart. LANSA Client knows the type of window you are saving and will automatically present the relevant dialog box to you.

If the data in the window has not been previously saved, you will be prompted to specify a file name for the new file. If the data has been previously saved, LANSA Client will overwrite the existing file with the new information.

Refer to the Save instructions for each type of window for more details about this command.

If you want to save the data with another file name, use the Save As command instead of this one.

Further Information

Ý 8.2 File Menu


8.2.6 Save As

Use the Save As command to save an existing query, result or chart window with a different name. Your original file will remain unchanged.

Further Information

Ý 8.2 File Menu


8.2.7 Close

This command will close the current set of windows. If the window contains data which has not been saved, you will be asked to confirm that you wish to close the window.

  • When you close a window, all its related windows will also be closed.

This command does not close LANSA Client. If you want to exit from LANSA Client, select the Exit command from the File menu.

Further Information

Ý 8.2 File Menu


8.2.8 Print

Use the Print command or [image] to print the information displayed in a Results window or a Chart window. You cannot print the contents of a Query window (i.e. a LANSA Client query's definitions).

You can use the Printer Setup command to select your printer before initiating the print command.

Further Information

Ý 8.2 File Menu


8.2.9 Printer Setup

The Printer Setup command allows you to set up your printer via the Print Setup dialog box. This command is only available if you have a printer attached to your workstation.

The style of the dialog box presented to you will depend on the type of printer you have connected to your workstation.

Further Information

Ý 8.2 File Menu


8.2.10 Generate

Using this command, you can generate a stand-alone application based on queries you have created and saved.

Once you have generated the application, you can run the queries included in the application without having to start LANSA Client first. LANSA Client does need to be installed on the workstation on which generated applications are to be run.

You do not have to write a single line of programming code to create the stand-alone applications using this facility. It is all done using LANSA Client's point-and-click approach.

Refer to Generating Applications for details of generating a LANSA Client application.

Further Information

Ý 8.2 File Menu


8.2.11 Disconnect

The Disconnect command will disconnect you from the current host system.

This command does not close LANSA Client. If you want to exit from LANSA Client, select the 8.2.12 Exit command from the File menu.

You use this command when you want to change from one host system or partition to another.

When the Disconnect command has been executed, the LANSA Client alternate window is displayed so that you can choose another host to connect to. Once the connection between your workstation and the host is established, you can use LANSA Client.

Further Information

Ý 8.2 File Menu


8.2.12 Exit

The Exit command closes LANSA Client. If there is any data which has not been saved, you will be prompted to confirm the exit. You can choose to cancel the closing of LANSA Client in order to save any of the unsaved data.

Further Information

Ý 8.2 File Menu


8.3 Edit Menu

The Edit menu contains this command:

  • 8.3.1 Copy

Further Information

Ý 8. Commands


8.3.1 Copy

The Copy command allows you to copy a Chart or a selected region of a Results window to the System Clipboard.

The data copied to the Clipboard is retained until it is overwritten by the next copy command. The original data is not disturbed.

If you are copying a Chart, then the whole chart is copied to the Clipboard. You cannot cut or copy a portion of a chart.

Further Information

Ý 8.3 Edit Menu


8.4 Options Menu

The Options menu contains various commands which you use to customize your query. Some of these commands can be used both before and after you have run your query.

The Options menu contains these commands:

  • 8.4.1 Group By
  • 8.4.2 Sort Options
  • 8.4.3 Summary Options
  • 8.4.4 Join Type Options
  • 8.4.5 Field Conditions
  • 8.4.6 Date Time Formats
  • 8.4.7 File Filter
  • 8.4.8 Order Files by
  • 8.4.9 Display Object Name
  • 8.4.10 Display Logical Files (Views)
  • 8.4.11 Display Physical Files
  • 8.4.12 Set Language
  • 8.4.13 View

Further Information

Ý 8. Commands


8.4.1 Group By

This command allows you to arrange your data in groups, based on one or more of the fields in the query. You can use it with both a Query and a Results window.

When you select the Group By command or press this [image] toolbar icon, the Group By dialog box is opened.

Use this dialog box to select the fields to which you want to apply grouping conditions and to specify whether the summary options you set in the Summary Options command are to be applied to these groups immediately.

You can expand or collapse your grouped results in the Results window, as described in the 8.6.6 Expand Group & Collapse Group commands in the Results menu.

Refer to LCL003 - Enhance Query Results, Report and Chart in the Tutorials for more details.

Further Information

Ý 8.4 Options Menu


8.4.2 Sort Options

This command allows you to specify the sequence in which you want to see the data. You can use this command when you are working with a Query window or a Results window.

Select the Sort Options command or this [image] toolbar icon to open the Sort Options dialog box where you specify your sort criteria.

LANSA Client gives you the option of displaying/not displaying the fields that you are using to sort the data.

If you change a query's sort options, the revised sorting criteria are saved when the query is next saved.

Further Information

Ý 8.4 Options Menu


8.4.3 Summary Options

This command allows you to attach calculations to fields in your query, either before or after the query is run.

Select the Summary Options command or the [image] toolbar icon, to open the Summary Options dialog box.

You can add one or more of these options to each numeric field:

Minimum | For the lowest number in the field/column.
Maximum | For the highest number in the field/column.
Average | The average value of the field/column.
Sum | The total of the field/column.
Count | The total number of occurrences of the field. Count can be used with alphanumeric as well as numeric fields.

Each calculated result is inserted in the Results window as an additional row with its own label in place of the Row Number. These rows are inserted at the end of the Result as well as at the end of each group, if groups have been specified using the Group By command.

For details, refer to LCL003 - Enhance Query Results, Report and Chart in the Tutorials.

Further Information

Ý 8.4 Options Menu


8.4.4 Join Type Options

This command allows you to specify the join type to be used between files joined in your query.

This command is only available from the Query Window when you have more than one file open at the current level in your query.

When you select the Join Type Options command, the Join Type Options dialog box is displayed. The default Join Type is displayed for each new join. The default is specified by the LANSA Client Administrator as described in Create a LANSA Client Environment in the LANSA Client Administrator's Guide.

From the drop down list, choose the type of join that you require. This can be:

  • All Records (Left Outer Join)
    Records from the primary file will be shown even if there are no matching records in the secondary joined file. For example, list customers with and customers without orders.
  • Matched Records (Inner Join)
    Records from the primary file will only be shown if matching records in a secondary joined file are found. For example, list only customers with orders.
  • Unmatched Records (Exception Join)
    Records from the primary file will only be listed if there are no matching records in the secondary joined file. For example, list only customers who have not placed any orders.

Press the Reset button to reset all joins to the default Join Type.

Further Information

Ý 8.4 Options Menu


8.4.5 Field Conditions

This command allows you to attach search conditions to a query. This command is available from the Options menu.

When you select this command or this [image] toolbar icon, the Field Conditions dialog box appears.

Refer to LCL003 - Enhance Query Results, Report and Chart in the Tutorials for more details about this option.

Further Information

Ý 8.4 Options Menu


8.4.6 Date Time Formats

This command allows you to select a date or time format for the input dates and times in your query. You also have the option to save the date or time as text, rather than have it formatted into a date format. The default format used when saving these items in Excel and dBase is specified on your PC's Client environment, so you don't need to be selected for each date or time.

The date or time format selected must match the format specified for that date or time in the LANSA Repository otherwise it will not be saved correctly.

This command is available from the Options menu and via this [image] toolbar icon. When you select this command, the Date time format dialog box opens for you to make your selection.


8.4.7 File Filter

This command allows you to create a filter to reduce the number of files displayed in the Files to Select From area of your Query window. Using a filter is useful when you must scroll through a large number of files to locate the one you want.

When you select this command, you are presented with the File Filter Options dialog box to specify the filter's options.

Once you have specified a file filter, whenever you start LANSA Client, only the files which match those specified in the filter will be displayed in the Query window.

Further Information

Ý 8.4 Options Menu


8.4.8 Order Files by

This command allows you to order the files displayed in the Files to Select From area of your Query window. You can choose between Nothing, Description and File Name.

Once you have specified a file order, whenever you start LANSA Client, the file order will be remembred.

Further Information

Ý 8.4 Options Menu


8.4.9 Display Object Name

This command allows you to toggle between displaying and not displaying the object names in the Query window.

If you choose to display the object names, they are added, in brackets, to the end of the descriptive file and field names.

Unless you prefer to use the object names in your system, this option should be turned off.

When LANSA Client is started, the file and field object names are displayed, or not displayed, as specified in the LANSA Client configuration file.

Further Information

Ý 8.4 Options Menu


8.4.10 Display Logical Files (Views)

This command allows you to toggle between displaying or not displaying logical files (i.e. different sequences of the file) in the Query window.

When LANSA Client is started, the logical files are displayed, or not displayed, as specified in the LANSA Client configuration file.

Note: Any logical files which are part of a linkage (Access Route) definition will be displayed, irrespective of whether this command is selected.

Further Information

Ý 8.4 Options Menu


8.4.11 Display Physical Files

This command allows you to toggle between displaying all or only Physical files in the Query window.

Further Information

Ý 8.4 Options Menu


8.4.12 Set Language

This command displays the Set Language dialog box, from which you select the language you want to use with LANSA Client.

Select the language that you want to see on the LANSA Client dialog boxes and windows.

LANSA Client is always available in English. Therefore, you will always see ENG in the list of languages.

Further Information

Ý 8.4 Options Menu


8.4.13 View

The View command has a sub-menu containing:

  • Tool Bar
  • Status Bar

By turning off the display of the Tool Bar and/or Status Bar, you can increase the size of the working area in your LANSA Client Query and Results windows.

Tool Bar

This command allows you to toggle between displaying or not displaying the Tool Bar.

A checkmark beside the command indicates that you have elected to display the Tool Bar.

Status Bar

This command allows you to toggle between displaying or not displaying the Status Bar.

A checkmark beside the command indicates that you have elected to display the Status Bar.

Further Information

Ý 8.4 Options Menu


8.5 Query Menu

The Query menu's commands are only available when you are working with a Query window.

The Query menu contains these commands:

  • 8.5.1 Execute
  • 8.5.2 Cancel Query
  • 8.5.3 Output To
  • 8.5.4 Output Options
  • 8.5.6 Linked Query Option

Further Information

Ý 8. Commands


8.5.1 Execute

Select the Execute command to run:

  • a new query.
  • an existing query which you have opened using the Open command.

The results of the query will be directed to the output device you have specified using the Output To command.

If you want to execute an existing query without changing it, choose the Run command in the File menu, instead of this command.

Further Information

Ý 8.5 Query Menu


8.5.2 Cancel Query

This command allows you to cancel the query in the middle of its execution.

It may take some time before the query is actually canceled. This depends on the Router you have installed on your workstation.

Further Information

Ý 8.5 Query Menu


8.5.3 Output To

Use this command to specify where the output of a query is to go. If you do not specify an output destination, your query's output will be displayed in a Results window.

Select an option from the Output To command's dropdown list:

  • Window (this is the Results window).
  • File (if you select this option you will be asked to nominate the file name and type).
  • Printer.
  • Report (you must have previously formatted a report).

Note: The output of a query with grouping conditions can only be directed to a Results window or to a printer.

The Results window will not be displayed if you select the File, Printer or Report output options.

Further Information

Ý 8.5 Query Menu


8.5.4 Output Options

This command allows you to vary a query's output.

If you have specified that the query's output is to a Window, File or Printer and not to a Report, the Output Options dialog box will be displayed.

Editing Options

These options give you the ability to change the data retrieved by your query for this particular LANSA Client session only. Editing is only possible if output is to a Window, otherwise the three options will be greyed out.

The default editing options are set by the LANSA Client Administrator in the LANSA Client Configuration file.
If the editing options have been disabled for your LANSA Client Configuration file, you will not be able to perform any editing functions at all, irrespective of what you select in this dialog box.

If one or more of the first three options is enabled, and if the query is against a single file, LANSA Client will automatically construct data entry screens to match the data selected for editing. No programming code is needed to use this feature.

Refer to Edit/Insert/Delete Capabilities for full details.

Allow Editing of Record

Select this option if the results of a query can be changed.

Allow Insertion of New Record

Select this option if new records can be inserted into the file.

Allow Deletion of Record

Select this option if records can be deleted from the file.

Maximum Records per Query

This option allows you to specify the maximum number of records that the query is to retrieve. It is useful to set this to a fairly low number when you are experimenting with search criteria.

This limit applies to the number of records transferred from the host to your workstation, not to the number of records read at the host. If this number is 0, all the data which meet your search criteria are retrieved.

Your LANSA Client environment may also contain a maximum records setting. If so, the smaller of the two numbers is used.

Initial Chart Options

By using this option, you can reduce the amount of customizing required for a new Chart window.

The Chart Options dialog box appears when you select this button. In this dialog box, choose the options that suit your most common requirements. For example, if you use a Pie Chart more often than any other type of chart, select the Pie Chart as the initial chart type for new Chart windows. You can also specify the initial font and font style to be used in the chart title, and so on.

Refer to Customizing a Chart for details of the options in the Chart Options dialog box.

Results File

If the query outputs to a file, the name of the file created when the query is executed is displayed. You can either enter another name, or you may select an existing file (and file type) by clicking on the Browse button. If the query is not output to a file, the Browse button and the Prompt for filename option will be disabled.

If the results has data from any Unicode fields the results file will be created in UTF-8 format.

Prompt for filename on query execution

If this option is checked (selected) then you will be prompted for an output filename when the query is executed, with the Results File name provided as a default. If this option is unchecked, the query will output to the Results File without prompting, overwriting it if it already exists.

Results File

Optionally specify a file to which query results are written. If the Prompt for filename on query execution checkbox is ticked, the user will be given an opportunity to choose a different file, otherwise this file will be overwritten every time the query is run.

If a filename is not specified here, the user will be prompted for an output filename each time the query is executed.

Batch File

Optionally specify the filename of a batch script to be executed after a query has executed. No arguments are passed to the script, and Client will not halt while the script is running.

Further Information

Ý 8.5 Query Menu


8.5.5 Output to Report Options

If you have selected Report as the location for your query's output, the Output to Report Options dialog box is displayed when you select the Output Options command.

Maximum Records

Set this value to restrict the number of records to be included in the report.

Leave blank or insert zero to if you don't wish to apply any restrictions.

Direct to (default) Printer

Select this option if you want your report to be printed at the printer defined as the default printer for your workstation rather than be displayed in a window (the default action).

Report to start after query executes

Optionally specify the name of a Crystal Reports file to be opened after the query runs. If this is left empty Client will try to open a report using the name of the query. For instance, if you run C:\Client\Example.lcq, it will look to open C:\Client\Example.rpt.

Database file to be created by query

Optionally specify the name and type of the database file to be created by the query. This should be the same file that the Crystal Report is using as its data source.

If the Database field is left blank Client will use the default database type defined in the Client Administrator, and use the name and location of the query file to determine its name. For instance, if the query is C:\Client\Example.lcq and the default database type is XML, it will create C:\Client\Example.xml.

Select OK to save your selection or Cancel to discard it.

Further Information

Ý 8.5 Query Menu


8.5.6 Linked Query Option

When you select this command, the Link Query Options dialog box appears so that you can:

  • assign a name to a linked query and link it to a report, if required.
  • link a report to a query when the report file name is different from the query name.

Refer to LCL006 - Build, Execute and Save a Linked Query in the Tutorials for step-by-step details of creating a linked query.

Further Information

Ý 8.5 Query Menu


8.6 Results Menu

The Results menu's commands are only available when you are using the Results window.

The Results menu contains these commands:

  • 8.6.1 Chart
  • 8.6.2 Linked Queries
  • 8.6.3 Edit Row
  • 8.6.4 Insert Row
  • 8.6.5 Delete Row
  • 8.6.6 Expand Group & Collapse Group
  • 8.6.7 Report
  • 8.6.8 Report Designer

Further Information

Ý 8. Commands


8.6.1 Chart

Select this command to open a new Chart window.

You can have up to 4 Chart windows open at the same time. Each Chart window can have a different type of chart displayed.

To view all the Chart windows at the same time, select Window from the menu bar and choose the 8.8.2 Tile command.

Refer to 8.7 Chart Menu and LCL003 - Enhance Query Results, Report and Chart in the Tutorials for details about charting with LANSA Client.

Further Information

Ý 8.6 Results Menu


8.6.2 Linked Queries

This command allows you to execute a query using data from the Results window as the search criteria.

When you select this command, you will see a sub-menu of the queries linked to the Query in the Results window. You can specify the names in the sub-menu using the 8.5.6 Linked Query Option command on the Query menu. If no name is assigned, the file name of the primary file is used.

When this command has executed the linked query, the results will be displayed in a new Results Window.

If there are no linked queries, this command will not be available.

Refer to LCL006 - Build, Execute and Save a Linked Query in the Tutorials for more about linked queries.

Further Information

Ý 8.6 Results Menu


8.6.3 Edit Row

When you highlight a row in the Result window and select this command, an Edit Row dialog box is presented to you so that you can change data.

This command is not available if:

  • the query contains more than one file.
  • this option is not enabled in your LANSA Client environment.
  • this option has not been enabled via the 8.5.4 Output Options command.
  • you are not authorized to change the database.

Note that there is no programming required to create this dialog box.

Key fields are formatted in read-only mode, so that you cannot attempt to change them.

Refer to Work with Query Results and LCL004 - Edit Data in the Tutorials for more about editing data.

Refer to

Further Information

Ý 8.6 Results Menu


8.6.4 Insert Row

The Insert Row command allows you to insert a new record into the file in the Results window. When you select this command, an automatically generated Insert Row dialog box is presented to you.

This command is not available if:

  • the query contains more than one file.
  • this option is not enabled in your LANSA Client environment.
  • this option has not been enabled via the 8.5.4 Output Options command.
  • you are not authorized to insert records into the database.

Note that there is no programming required to create this dialog box.

Refer to Work with Query Results and LCL004 - Edit Data in the Tutorials for more about editing data.

Further Information

Ý 8.6 Results Menu


8.6.5 Delete Row

The Delete Row command allows you to delete a selected row of data from the file. This command is not available if:

  • the query contains more than one file.
  • this option is not enabled in your LANSA Client environment.
  • this option has not been enabled via the 8.5.4 Output Options command.
  • you are not authorized to delete records from the database.

Refer to Work with Query Results and LCL004 - Edit Data in the Tutorials for more about editing data.

Further Information

Ý 8.6 Results Menu


8.6.6 Expand Group & Collapse Group

If you have specified grouping conditions, the Expand and Collapse Group commands allow you to expand and contract the grouped results. Before you can expand or contract information, you must first specify the groups in which you wish to group your data, as described in the 8.4.1 Group By options.

Use the Collapse Group command to hide the display of the query's details so that you can easily see the group totals. Then use the Expand Group command to see the details again.

Using the Collapse Group command, you can collapse the rows until only the grand totals are displayed.

Further Information

Ý 8.6 Results Menu


8.6.7 Report

Use this command to display the results of your query in a predefined report format.

If you have a predefined report format file with the same name as the query, then the results will be immediately displayed in that report's format.

If LANSA Client cannot find a report format for the query, Crystal Reports will be invoked so that you can design one.

If your results have been collapsed using the Collapse Group command, then this command will not be available to use until your data has been fully expanded using Expand Group command.

To use this command, Crystal Reports must be installed on your workstation.

If Crystal Reports is not installed on your workstation, this command will not be available and you will not be able to use the reporting facility. Instead, you will be asked if you would like to use the sample report provided with Step 4. Display the Results using a Report Format in the LANSA Client Tutorial: LCL003 - Enhance Query Results, Report and Chart.

Further Information

Ý 8.6 Results Menu


8.6.8 Report Designer

Select this command to create a new report or amend an existing report.

This command will call Crystal Reports. This is an extremely powerful What-You-See-Is-What-You-Get (WYSIWYG) reporting tool allowing you to design sophisticated reports easily using point-and-click commands.

Once the report is designed, it is saved as a Database File (in DBF/MDB format as per default) for later use.

In order for LANSA Client to find the report format belonging to your query, you should assign the same name to the report format as you have assigned to the Query that will use it. If a different query and report name must be used, then you can specify the link between them using the 8.5.6 Linked Query Option command in the Query menu.

Refer to the Crystal Reports manual for details about using Crystal Reports.

Further Information

Ý 8.6 Results Menu


8.7 Chart Menu

The Chart menu's commands allows you to customize a graph or chart or to select a different type of chart. They are only available from a Chart window.

The Chart menu contains these commands:

  • 8.7.1 Options
  • 8.7.2 Chart Types...

Further Information

Ý 8. Commands


8.7.1 Options

Select the Options command when you want to modify the chart in the current Chart window. When you select this command, you will be presented with the Chart Options dialog box to specify your changes.

The chart sample in the upper right corner changes to show you the effect of the changes as you make them.

Refer to LCL003 - Enhance Query Results, Report and Chart in the Tutorials and the 8.7.2 Chart Types... for details about charting with LANSA Client.

Further Information

Ý 8.7 Chart Menu


8.7.2 Chart Types...

When you select the Chart menu, you are presented with a drop down list of all the LANSA Client chart types. Simply select the chart type you require and your data will be immediately re-charted with the new chart type.

You can specify the chart type to be a:

  • 2-Dimensional Pie Chart
  • 3-Dimensional Pie Chart
  • 2-Dimensional Bar Chart
  • 3-Dimensional Bar Chart
  • Line Chart
  • Log Linear Chart
  • 2-Dimensional Area Chart
  • 3-Dimensional Area Chart
  • Scatter Plot Chart
  • Polar Chart
  • Tape Chart

If you wish to view your data in more than one type of chart, open a new Chart window by choosing the New Window command in the Window menu on the menu bar.

Refer to Chart Results in LCL003 - Enhance Query Results, Report and Chart of the Tutorials for details about creating charts.

Further Information

Ý 8.7 Chart Menu


8.8 Window Menu

The Window menu's commands allow you to organize the presentation of the windows on your screen.

This menu contains these commands:

  • 8.8.1 New Window
  • 8.8.2 Tile
  • 8.8.3 Cascade
  • 8.8.4 Freeze
  • 8.8.5 Arrange Icons

Further Information

Ý 8. Commands


8.8.1 New Window

This command opens a new Chart window. It is only available when you are working with a Chart window.

You can have up to four (4) Chart windows active for a single set of results. For each of the Chart windows, you can select a different chart type and customize each one individually.

If you close a Results window, any Chart windows attached to the Results will also be closed.

Further Information

Ý 8.8 Window Menu


8.8.2 Tile

Use this command to see all your active LANSA Client windows at the same time. You can resize any of the windows if you wish.

Further Information

Ý 8.8 Window Menu


8.8.3 Cascade

Use this command to display all your current LANSA Client windows in a cascading manner so that you are still able to see each window's Title Bar. Click on the Title Bar to bring a window into the foreground.

Further Information

Ý 8.8 Window Menu


8.8.4 Freeze

This command allows you to freeze (make non-scrollable) any number of columns and/or rows in the Results Window. The frozen columns are always to the left of the selected column or cell. The frozen rows are always above the selected row or cell.

To freeze a column and all the columns to the left of it, click on the column's heading in the Results Window and then select Freeze from the Window menu.

To freeze a row and all the rows above it, click on the row number on the left of Results Window and then select Freeze from the Window menu.

To freeze both the columns to the left and the rows above a cell, select the cell and select Freeze from the Window menu.

When an area is frozen, axes are displayed as straight lines splitting the screen. The areas to the left or above these lines will be frozen and will not scroll.

A tick appears next to Freeze in the menu to indicate that freezing has been applied to current Results Window. To unfreeze select Freeze (with the tick mark) from the Window menu.

Further Information

Ý 8.8 Window Menu


8.8.5 Arrange Icons

If there is an open document window at the bottom of the main window, then some or all of the icons may not be visible because they will be underneath this document window. Use this command to arrange the icons for minimized windows at the bottom of the main window.

Further Information

Ý 8.8 Window Menu


8.9 Help Menu

The commands on the Help menu provide you with helpful information about LANSA Client.

The Help menu contains these commands:

  • 8.9.1 Help Topics
  • 8.9.2 About LANSA Client...

Further Information

Ý 8. Commands


8.9.1 Help Topics

This command opens a version of the LANSA Client User Guide. This version is intended for use with context help. To navigate around this version of the guide, you can use the For Information links at the bottom of each page. The green Back and Forward buttons on the page will retrace links that you have followed.

If you don't know the context of the topic you wish to locate, you can Search for it. To do so, open the main guide by pressing the blue Contents button at the top of the page. Note that you can open the main guide from any page using this Contents button provided you have a copy of the LANSA online documentation on your PC.

To search for a topic or item, simply select the Search tab and enter your search criteria in the Type in the word(s) to search for box and press List Topics.

If you have more than one word, then enclose the words in double quotes, otherwise each occurrence of each word will be found. Select the Search titles only option if you know that you are looking for a topic's title. If you have too many responses from your search, you can narrow it down by further searching on the selection displayed and selecting the Search previous results option.

Further Information

Ý 8.9 Help Menu


8.9.2 About LANSA Client...

This command presents you with a dialog box which tells you the version of LANSA Client, LANSA Open and LANSA Communications you are using.

Optionally the version of the language DLL used will be shown.

Further Information

Ý 8.9 Help Menu


9. Troubleshooting

Note: Communication errors, if any, occur in the LANSA Open portion of LANSA Client.

You may need to refer to the Error Codes in the LANSA Communications Setup Guide or the LANSA Open Error Codes.

If You Encounter this Problem...

For ease of use, the questions you may have are divided into these groups:

9.1 Getting Started

9.2 LANSA Client

9.3 Error Messages

9.4 Crystal Reports


9.1 Getting Started

You cannot find the LANSA Client Program Folder

LANSA Client may not have been installed properly. Reinstall LANSA Client.

You cannot find the LANSA Client Administrator

The LANSA Client Administrator has not been installed on your workstation. Check with your administrator, as it is possible that the Administrator should not be installed on your workstation.

Not all the files in your host system are displayed

Only the files you are authorized to use are displayed in the LANSA Client Query window. File access is controlled by your host's administrator.

Further Information

Ý 9. Troubleshooting


9.2 LANSA Client

When you run a top ten style query, you get ten records, but not the top ten

For LANSA Client to perform a "top ten" query correctly, the records must be sorted on the host before the first 10 (or whatever number you have selected) are returned to the PC.

When you run a generated application, you are still asked to select the Host even though you specified a particular host for your application

This will occur if you change the LANSA Client environment and do not close and open LANSA Client before you generate the application. You will need to re-assign the host by opening the application, reselect the host and re-generating the application.

When retrieving a large volume of data, LANSA Client runs very slowly

You should always try to avoid retrieving large volumes of data. There are a number of reasons why this could occur. Most of them are unrelated to LANSA Client. First check that your query conforms to the recommendations in Performance Consideration and if you see no obvious reason for the query to run very slowly, contact your System Administrator.

You get a "General Protection Fault" message when attempting to use the Report command

You may have an incompatible version of Crystal Reports installed.

Data appears to be missing from the query you have executed

The Maximum Records Allowed option has been set in your LANSA Client environment to a lower number than you need for this query. This option is used to control the number of records transferred from the host. The same limit can also be set for this workstation using the Output Options in the Query menu.

You can either raise the limit by changing the Maximum Records Allowed or remove the limit altogether by changing it to zero.

The HotGraphing facility in LANSA Client does not work

HotGraphing may have been turned off in the Chart Options. If this is not the case, make sure that you are using the SPR3270.DLL in the LANSA Client directory. Delete any older versions you may have on your workstation.

An existing report does not appear to work any longer. When the report is executed, no data is displayed even though data was retrieved from the host

The type of data saved for the column headings of the report has been changed from field description to field label or vice versa.

Once the choice has been made to display either the field description or the field label, it should not be changed.

Please note that LANSA Client Version 1 used the labels as the column headings whereas, by default, later versions used the field descriptions.

Using the LANSA Client Administrator, change the column heading to use field labels, re-run the query again and save the data. Your existing report should now work.

When you run a prompted query, you cannot select the dropdown list or specify a value

Make sure that you are using the SPR32D70.DLL from the LANSA Client directory. Delete any older versions you may have on your workstation.

Further Information

Ý 9. Troubleshooting


9.3 Error Messages

"Internal Query Error"

This message is widely used to indicate unexpected situations encountered during the creation or execution of a query. Possible reasons for such a message are:

  • Virtual Fields could not be retrieved.
  • Select statement could not be generated.
  • Parts of the select statement could not be generated.
  • Certain internal pre-requisites that need to be in place in order to run the query did not have the expected settings (e.g. pointers set to NULL where they should not be NULL).
  • The execution of a query failed (comms. error will be displayed followed by Internal Query Error).

If you encounter this error message then please try to reproduce the steps that produced it, then contact your database administrator for assistance.

"LCE0035 - File(s) defined in LceUseField function not defined in the LANSA data dictionary"

The files you are trying to access have not been imported into the partition on the IBM i.

"Query cannot be loaded" when you try to open or run an existing query

The existing query may have been created using files in another partition or the file definitions have been changed at the host since the query was created.

"LCE0023 - A Communications Error has occurred for the action, xxxxx. The return code is yyyyy"

For the cause of the error, refer to the return codes in the Internal Error Reason Codes list.

"You are licensed at the Server to use this product. However, the maximum number of users are already using it"

The number of users licensed to use LANSA Client simultaneously has been exceeded.

"File in saved query does not exist on host"

The existing query has been built using files in a different partition or you are no longer authorized to use the file specified in the query.

"Internal Error"

This message is displayed if an exception is found during the process of saving results to a file. Such an error normally originates from the database.

If you encounter this error message then please try to reproduce the steps that produced it, then contact your database administrator for assistance.

Further Information

Ý 9. Troubleshooting


9.4 Crystal Reports

Error message "Failed to load database information" is displayed when you try to run an existing query that outputs results to report

If the report has been created using an older version of Crystal Report, then this message indicates that Crystal Report hasn't installed the driver that is needed. The following steps will rectify the problem.

1. In the LANSA Client main window, select the Results menu and then choose Report designer

2. In Crystal Report, open the report file that you tried to execute.

3. Select the Database menu, then Verify Database, and Crystal Reports will trigger the installation program to install the required driver.

If the installer is not invoked, you can run a custom Crystal Report setup and elect to install the Data Access features.

Error message, "Logon failed'" is displayed when you try to run an existing query that outputs results to report

This can happen when the report was created using an older version of Crystal Reports - it is believed that this issue only arises for reports created in Crystal Reports versions before version 8.5 (version 8.5 of Crystal Reports was supplied with LANSA Client version 10.0).

In that case, this message may indicate that the database related to that Crystal Report file cannot be found. Please perform the following steps to rectify the problem:

1. Start Crystal Report.

2. Open the Crystal Report file that caused the error.

3. Select the Database menu and choose Set Datasource Location.

4. Locate the correct database file and press Update.

5. Save the Crystal Report file.

6. Re run the query.

The data displayed in the report does not match the data returned from the query

If you select the Save data with report option in Crystal Report's File menu, Crystal Reports will save the report together with the data. The next time you run the report, it will contain the saved data regardless of whether you have run your query again.

To ensure that your report contains the latest data, when you save a report format, ensure that this option is off, so that the Report looks for the latest query results to report.

If the "Save data with report" option is ticked, you can still refresh the data in your report by selecting the Refresh button (i.e. the lightning bolt).

Your Crystal report is showing incorrect data when the query is run

1. Press the refresh data button (the lightning bolt).

2. Open the report using Crystal Reports and check that the "Save Data with Report" (under File) menu option is NOT checked.

3. Check that the dBASE or MDB file from which Crystal is getting its data is the file that you want to use. Do this by selecting the Set Location (under Database) menu option and check the name of the file in the Location section of the dialogue window.

Your Crystal report is showing incorrect data when query is run in a generated application

A generated application executes and stores its data in the LANSA Client Applications Directory. By default this directory is called LANSA\APPS.

When a report is part of a generated application, make sure that the report gets its data from the LANSA Client Applications directory.

You can check this by selecting the 'Set Location' command from the Database menu in Crystal Reports.

Further Information

Ý 9. Troubleshooting


LANSA Client Tutorials

These tutorials cover the key functions provided by LANSA Client. In order to show you how to use the functions efficiently, they have been split into separate tutorials that are interrelated. That is, the results of one tutorial will often be used in the next tutorial.

These are the tutorials provided:

  • LCL001 - Start LANSA Client
  • LCL002 - Run a Simple Shipped Query
  • LCL003 - Enhance Query Results, Report and Chart
  • LCL004 - Edit Data
  • LCL005 - Build, Execute and Save a Join Query
  • LCL006 - Build, Execute and Save a Linked Query
  • LCL007 - Generate an Application to create a "mini" stand-alone application.
  • LCL008 - Create a LANSA Client Environment. This tutorial is optional as the environment may already have been created for you.

Specific LANSA Client steps you need to perform in sequence are preceded by a number. For example:

1. Select the LANSA Client Tutorial from the list and press the OK button. Alternatively, you could double-click on LANSA Client Tutorial.

  • Points to note are preceded by a bullet point.

Comments are in normal text without a bullet point.


Before you Begin

Before you can use the tutorial examples you will need to get ready the PC and the tutorial files that you will be using. Before starting the tutorials, complete this checklist.

  • Has LANSA Client been installed on your PC?
  • Has SAP Business Objects been installed on your PC? This is optional. If you don't have Business Objects installed, you will bypass Step 4 of tutorial LCL003.
  • Have you access to the files used for the Tutorials?

Instructions for setting up these files are in Set Up the Tutorial files on an IBM i Host or Set up the Tutorial files on a Windows Host.

  • Is the Tutorial partition, which comes with the host-based programs for LANSA Client, available to your PC?

If you are using a standalone PC, then you will most likely have the necessary files available to you. If the LANSA Client Tutorial is not in the list of hosts to link to, ask your system administrator to create a LANSA Client Environment file for you. You can also create one yourself as described in LCL008 - Create a LANSA Client Environment .

  • Is the communications software successfully connecting your PC to the host?
  • Have you a printer attached to your PC? (This is optional for the tutorial.)
  • Is Windows started?

If you can answer Yes to all of these questions, you are ready to start using the tutorial.

It is assumed throughout this tutorial that you are familiar with Microsoft Windows software.

Go to LCL001 - Start LANSA Client.


LCL001 - Start LANSA Client

1. Select the LANSA Client menu from the Windows Start menu.

2. Click on the LANSA Client icon in the LANSA Client list to start LANSA Client.

If you can connect to more than one host system, the Select Host dialog box will be displayed.

3. For this tutorial, select LANSA Client Tutorial (LCL) from the list and press the OK button. Alternatively, you could simply double-click on LANSA Client Tutorial line.

If you only have one host system to connect to, this selection step will be bypassed.

When a connection between the PC and the host you have selected is established, the LANSA Client Main Window is displayed, otherwise the LANSA Client Alternate Window is displayed.

4. When the LANSA Client Main Window is displayed, go to LCL002 - Run a Simple Shipped Query.

About the LANSA Client Main Window

Start all LANSA Client functions, such as to create a query, run a query and so on, from the LANSA Client main window.

The LANSA Client main window, as with all other LANSA Client windows, has standard Windows facilities which include:

  • Maximize and minimize buttons in the top right corner.
  • The system menu icon in the top left corner.
  • A title bar across the top of the window.
  • A menu bar just below the title bar.
  • A tool bar just below the menu bar.
  • A status bar at the bottom of the window. The status bar is used to display:
  • information about the file you have selected
  • the host system and partition you are currently connected to
  • the status of your query, when it is running.

LANSA Client Alternate Window

If a connection cannot be established to the host, the LANSA Client alternate window is displayed. In this case, you will need to restart the signing on process, after you have investigated the reason for not connecting to the host.


LCL002 - Run a Simple Shipped Query

A sample LANSA Client query is supplied with LANSA Client. This sample query retrieves data from only one file, so it is called a "simple" query.

Running a simple query requires these steps:

Step 1. Open an Existing Query

Step 2. Attach Selection Conditions to the Query

Step 3. Execute the Query

Once you have created the query, you can then go on to:

LCL003 - Enhance Query Results, Report and Chart


Step 1. Open an Existing Query

1. Select the File menu choose the Open command. Alternatively, you could select the [image] Open an existing query icon on the Tool Bar, or use the accelerator key combination (Ctrl + O) that is shown beside the Open command.

The Open Query dialog box is displayed.

The Open Query dialog box contains a list of existing LANSA Client queries.

2. Select the LCSample.LCQ, the Sample Query.

3. Press the OK button to open the Sample Query.

The Query window showing the Sample Query is displayed.

Note that in this window, the field and file names are displayed beside their field and file descriptions. To remove these names, from the Options menu, select Display Object Names and click on it to remove the tick. Your Query window will now look like this:

In the Query window, you will see the "definition" of the query that you have opened. That is,

in the Selected File area:

  • the Name of the primary file selected for the query, in this case, the Sales History file.
  • all the fields in the Sales History file.
  • the fields selected for the query. They have a tick over their icon.

in the Selected Fields area:

  • the fields that have been selected for this query.

in the Defined Links to Queries area:

  • the Sample Query. Only this query is displayed as there are no linked queries.

As you can see, the Sample Query will retrieve the Sales History for all Products for January, February and March.

4. Now go to Step 2. Attach Selection Conditions to the Query.


Step 2. Attach Selection Conditions to the Query

For the Sample Query, you want to retrieve the sales history for only some countries. To do this, you attach a selection condition to the Sales Country.

1. Select the Options menu from the menu bar and choose the Field Conditions command. Alternatively, you could select the [image] Field Conditions icon from the Tool Bar.

The Field Conditions dialog box is opened.

In the Field Conditions dialog box you can attach selection conditions to the fields in your query.

The sample query already has a selection condition defined. In this case, all Countries except the US are selected. To edit a condition, highlight it, then re-select the Operators and Values.

You can see that a field has a condition attached when there is an asterisk (*) to the left of the field's name in the Selected Fields area of the Query window. Do you see the asterisk next to Sales Country in this tutorial's Query window?

2. Press the Cancel button to dismiss the Field Conditions dialog box.

You are returned to the Query window.

3. Go to Step 3. Execute the Query


Step 3. Execute the Query

1. Select the Query menu and choose the Execute command. Alternatively, you could select the [image] Execute (run) the current query icon from the Tool Bar or use this command's accelerator keys (Ctrl + E).

LANSA Client will now execute the query on the host system. While it is doing this it will display messages in the Status Bar to inform you of the progress of the query.

The results of the query are displayed in a spreadsheet-like data viewer. This is known as the Results window.

You have just successfully used LANSA Client to run a query to retrieve data from the database.
Leave this query open, to use in LCL003 - Enhance Query Results, Report and Chart.


LCL003 - Enhance Query Results, Report and Chart

LANSA Client allows you to present your data in a variety of ways. Once you have a query's results in the Results window, you can:

  • Add totals, sort the data, group it and contract or expand it
  • Display it in a report format
  • Display it as a chart or graph
  • Copy some of it onto the System Clipboard and paste it into another application
  • Change or delete the data and insert new records.

Each of these features is described in detail in Creating and Running Queries.

Before you can do this tutorial, you will need to have created the sample query as described in LCL002 - Run a Simple Shipped Query.

When your query is ready to run, you can learn ways to enhance it using these steps:

Step 1. Add Summaries

Step 2. Group your Results

Step 3. Contract and Expand your Results

Step 4. Display the Results using a Report Format

Step 5. Chart Results

Step 6. Customize the Chart

Step 7. Use the Hot Chart Facility


Step 1. Add Summaries

Now to define summaries for your results. A summary can be the result of an evaluation, a summation or a calculation.

1. First, with your mouse pointer somewhere in the Results window, right click on your mouse to open the pop-up context menu.

2. Select the Summary Options command to open the Summary Options dialog box. In this dialog box, you can define the summaries you require for the numeric fields in your query. Alternatively, you can open the Summary Options using this [image] icon on the toolbar.

3. Request totals for January through to March by clicking the Average and Sum options for each of those months. A tick will be inserted when each box is selected.

4. Request a Count of the records by clicking on the Count option for the Sales Warehouse.

5. When done, press the OK button.

You are returned to the Results window. At the bottom of this window you will see a new row for each summary you have requested.

6. Go to Step 2. Group your Results.


Step 2. Group your Results

A group is a set of records that are related to each other in some way. In the files supplied for this tutorial, the records are related by country and warehouse.

1.Close the Results window and open the Field Conditions dialog using the [image] button on the toolbar.

2.In the list of Conditions, select the Sales country is equal to NZ and delete it.

3.Press the OK button to close the Field Conditions dialog.

4.Run the Query to produce results for all Sales Countrys, except US.

5. Display the pop-up menu of available commands by clicking on the right mouse button.

6. Select the Group By command to open the Group By dialog box. Alternatively, you could select it using the [image] icon on the toolbar.

All the fields defined for the query are displayed in the Query fields list in the Group By dialog box.

7. You are going to group the data according to Sales Country.

a. To do this, double-click on the Sales Country in Query fields or alternatively, select the Sales Country and click on the Add button. You will notice that Sales Country is immediately displayed in the Group By fields list.

8. Select Summary Options apply to grouped fields option, to have the summary totals calculated for each group.

9.When done, press the OK button.

The Results window will now show the new results.

10.Scroll through the Results window. You will see that there are additional rows for each Sales Country. This is the result of applying the grouping conditions.

Notice that at the end of the results you still have the totals which apply to all the records in the query.

11.Go to Step 3. Contract and Expand your Results


Step 3. Contract and Expand your Results

You can further manipulate the presentation of your data by collapsing and expanding the rows in the Results window.

1. Click on the right mouse button to bring up the pop-up window and select Collapse Group command.

2. You can repeat this command until only the final totals are shown.

3. Select the Expand Group command from the pop-up window to see the effect on the data in your Results window.

Leave the results fully expanded for the next step, Step 4. Display the Results using a Report Format.


Step 4. Display the Results using a Report Format

In this step, LANSA Client uses SAP Business Objects, a powerful analytical and report writer tool, to display the report. If you don't have Business Objects installed, then bypass this step.

A report for the Sample query is supplied. This step commands LANSA Client to find the Sample report format and display the results in the report's layout.

1. While your results are expanded, select the Results menu from the menu bar and choose the Report command. Alternatively, you could select the [image] Report icon from the Tool Bar.

A program is launched with the results of the query in the report layout specified for the query. You can print the report if you have a printer attached to your PC.

In this report, the monthly sales are summarized by quarter. This is achieved by defining formula fields when you create the report layout using the report writer.

So that LANSA Client can find the report belonging to a query, the query, its results and the report format file share the same file name, LCSAMPLE. That is:

  • LCSAMPLE.LCQ is the query's definition.
  • LCSAMPLE.MDB is the query's results.
  • LCSAMPLE. RPT is the query's report.

2. Close the Report window.

Leave the Results window active, so that you can continue to Step 5. Chart Results.


Step 5. Chart Results

Before you can create this chart, your Result window must have the focus and all the details must be displayed.

1. If the Result window does not have the focus, click on its title bar.

2. If the details are not all displayed, select the Expand Group command.

Now you will chart the sales figures for WHSM (Sales Warehouse M) in Argentina (AR).

3. Select all Sales Warehouse M data by clicking on the row number of each WHSM row while holding down the Shift key or alternatively, click and drag the cursor over the WHSM rows.

4. When all the cells you require have been highlighted, select the Result menu and choose the Chart command. Alternatively, you could select the [image] Chart icon on the Tool Bar.

A Chart window is displayed showing the rows you selected as a 3-dimensional Bar Chart. This is LANSA Client's default chart type.

5. Change the Chart Type. Select the Chart menu and choose a different chart type from the list. For example, select the Line command.

Notice that the Chart window instantly displays the same data in a line chart.

Note: Pie charts are not suitable to use when you have selected more than one column to chart.

6. Now go to Step 6. Customize the Chart.


Step 6. Customize the Chart

1. Select the Chart menu and choose the Options command. Alternatively, you could select the [image] Configure Chart Window Options icon on the Tool Bar.

The Chart Option dialog box is displayed.

2. In the Chart Options dialog box, change as many details of the chart as you would like. For example:

  • Change the title of the chart by typing Sales Totals First Quarter in the Title box.
  • Change the chart type to a 3-dimensional Bar chart by selecting the 3D Bar Chart from the Chart Type list.
  • Change the 3D Bar chart to Horizontal by selecting Horizontal from the Chart style list.
  • Select the Product Code for the Legend.

Have you noticed that each time you change an option associated with the chart, the sample chart displayed in the right hand corner of the dialog box reflects the new option?

3. Continue to change options to see the effect.

4. When done, press the OK button. You are returned to the Chart window.

Note that the Chart window now displays your new options.

5. Retain this chart as a 3-dimensional Bar chart for the next step, Step 7. Use the Hot Chart Facility.


Step 7. Use the Hot Chart Facility

LANSA Client's Hot Charting facility allows you to instantly find the actual data in the Results window that makes up any portion of a chart.

To use the Hot Charting facility, you need to display both the Results and Chart windows from the preceding steps. If you no longer have your chart, chart the results again as described in Step 5. Chart Results.

1. Minimize the Query Window.

2. Select the Tile command from the Windows menu to display your windows side by side.

Now you are ready to Hot Chart. Let's say that you want to look at the actual figures that make up the portion of your chart for Product IBM4033 for the month of February.

3. To do this, click on the February bar of Product IBM4033.

Did you see that the corresponding row of data was immediately highlighted in the Results window?

4. Try selecting another portion of your chart to interrogate.

5. When you have finished, close the Chart window.

6. Keep your Results window open for the next tutorial, LCL004 - Edit Data.


What I should know

Having completed Tutorials LCL001-LCL003, you should now know:

  • How to run a simple query.
  • How to open an existing query.
  • How to attach selection conditions to a query.
  • How to execute a query.
  • How to add Summary totals to a query.
  • How to group a query's results.
  • How to collapse and expand a query's results.
  • How to view a query's results as a report.
  • How to create and change a chart.
  • How to use the Hot Charting facility.


LCL004 - Edit Data

If you are starting the Tutorial at this point, you will first need to go through the steps in LCL002 - Run a Simple Shipped Query and Step 3. Execute the Query before you can continue.

To complete these steps your LANSA Client environment must be set up to allow update, insert and delete to the database files being accessed. Within LCL008, see Define the Host & its Options.

Now to change the data you have retrieved from the host. This is done from the Results window, so you need to make it the active window.

1. If the Results window is not the active window, click on its title bar.

2. If the details are not all displayed, use the Expand Group command.

3. Select any row in the Results window by clicking on its Row Number.

4. From the Results menu and choose the Edit Row command or right click to open the pop-up menu and choose the Edit Row command.

When the Edit Row dialog box opens, it is automatically formatted to match the data in the row you have selected.

You can change the details displayed provided you have the authority to do so. You will not be able to change the key fields such as the Product Code, Sales Country and Sales Warehouse.

5. When you have changed some details, press the Update button.

6. Press the Next button to display the next row. You do not need to return to the Results window to select another row.

If you are changing a record with many fields, you may need to use the scroll bar located to the right of the box to scroll down to the bottom.

You can obtain the Help text from the Repository on the host system for any field in the Edit Row box.

7. To get this help, press the Shift and F1 keys at the same time. A question mark will be attached to your cursor's pointer. Drag the ? to the Product Code box. When you release the mouse button, LANSA Client will retrieve the help text for the Product Code from the Repository and display it in a pop-up window in the language defined for the host system.

Can you see how really useful LANSA Client can be? You did not have to define any of these screens, any of the business rules or any of the help text.

As the business rules and your authority for making changes are defined in the Repository, you will not be able to change data or bypass the business rules to make unacceptable modifications under any circumstances. This facility is described in detail in Working with Query Results.

8. To dismiss the Help window, press the OK button.

9. Press the Close button to dismiss the Edit Row dialog box.

10.Return to the Query window and select the Close command from the File menu on the menu bar. All related windows will also be closed.

You have just finished the first part of the Tutorials. The next Tutorial is LCL005 - Build, Execute and Save a Join Query.


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 Sample query as you did before, by selecting the File menu on the Main Window and choosing the Open command.

2. When the Open Query dialog box appears, select the LCSample.lcq (Sample Query) and press the OK 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 Selected File 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 Selected File area of the Query window and select it by double-clicking on the file name.

The fields for the Country file will be immediately displayed in the Selected File area.

4. Add the Country Description to your query by dragging the Country Description from the Selected File area and dropping it into the Selected Fields 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 Field Conditions command from the Options 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 Results 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.


Save the Query

1. Select the Query window's title bar to make it the active window.

2. Select the File menu from the menu bar and choose the Save As command to open the Save Query dialog box.

3. Give the query's file a name, for example, My Tutorial Join Query in the File Name.

4. Press the OK button to save the query.

The Save Query dialog box is dismissed and you are returned to the Query window.

5. Close the Query by selecting the Close command from the File menu.

This query will be used in the tutorial LCL007 - Generate an Application .


LCL006 - Build, Execute and Save a Linked Query

A linked query is made up of two or more queries linked together. This allows the results of one query to be used as the selection criteria for another query, giving you the ability to drill down into your data only when you want to. With linked queries, you don't have to retrieve large amounts of data in case you may want to look at the details of a particular item.

In this tutorial you will create a query to obtain a list of Products. From this query you will randomly select a product and then you will Run the Linked Query as a Sales by Product query to obtain the sales of the selected product.

To do this you must perform these steps:

Step 1. Create the first query

Step 2. Select the fields for the query

Step 3. Define the link for the second query

Step 4. Select the fields for the linked or secondary query

Step 5. Save the Linked Query

Step 6. Run the Linked Query


Step 1. Create the first query

You will start with a query using the Product file, so that you can see all the products that are available.

1. To create a new query, select the File and choose the New command. Alternatively, you could select the [image] associated icon on the Tool Bar or use the accelerator keys (Ctrl + N).

When a new Query window is displayed, the:

  • Selected File area shows you all the files you can access.
  • Selected Fields area shows you the fields included in the query. Initially this area will be blank.
  • Defined Links to Queries area shows the queries that are linked together. An indented query indicates that it can be called by the query above it. Initially this area will be blank.

2. In the Selected File area, select the Product file by double clicking on its name.

Immediately, the fields in the Product file are listed beneath the file name and the Product file's name is inserted into the title bar of the area. This file is now identified as the primary file of your query.

3. Now go to Step 2. Select the fields for the query.


Step 2. Select the fields for the query

1. Drag the Product Code from the Selected File area into the Selected Fields area.

2. Drag the Product Description from the Selected File area into the Selected Fields area.

You have just created a query that will list all the available products.

3. Now go to Step 3. Define the link for the second query


Step 3. Define the link for the second query

1. Locate the Sales History By Product file in the Selected File area and drag it into the Defined Links to Queries area.

When your mouse button is released, in the Defined Links area you will see the Sales History by Product file indented beneath the folder representing the first query.

The fields you moved into the Selected Fields area are no longer displayed.

2. Now you must select the fields for the secondary query as shown in Step 4. Select the fields for the linked or secondary query.


Step 4. Select the fields for the linked or secondary query

1. Drag the Product Code, Sales Country, Sales Warehouse, January, February and March, one by one, from the Selected File area into the Selected Fields area. Alternatively, you could select them by double clicking on their name in the Selected File area.

2. Finally, you will save the query, as described in Step 5. Save the Linked Query.


Step 5. Save the Linked Query

1. Select the File menu and choose the Save command. Alternatively, you could select the [image] Save icon in the Tool Bar, or use the accelerator keys (Ctrl +S).

The Save Query dialog box is displayed.

2. Give the query a file name, such as TUTLINK.LCQ in the File Name.

3. Press the OK button to save the query. You will be returned to the Query window.

Have you noticed that the name of the primary query in the Defined Linked Area now has the Query Description you have entered?

You have just created and saved a linked or drill down query, from the Product file to the Sales History by Product file.

4. Now, run your query, as described in Step 6. Run the Linked Query.


Step 6. Run the Linked Query

First run the Primary Query

1. Execute the Tutorial Linked Query. Select the method of executing a query which you prefer or return to Step 3. Execute the Query for a reminder.

All the products in the Product file will be displayed in the Results window.

Select a product for the Linked Query

Imagine that you want to look at the sales figures of a particular product, say, the IBM4079 (Color Ink Jet) product.

1. In the Results window, select the row of data which contains the IBM4079 product.

2. To execute the linked query, select the Results menu and choose the Linked Queries command. This will display a drop down menu.

3. Select the Sales History By Product command from the drop down menu. This will execute the secondary query using the IBM4079 product code as its selection criteria.

A new Results window is displayed to show you the sales history of the IBM4079 product.

4. Repeat the process for another product to see how LANSA Client executes the linked query for a different product code.

As you have seen, LANSA Client provides you with the ability to build queries which can span multiple files and allow you to drill down from one file to another, without you having to write a single line of programming code.

5. Close the Linked Query.

This is the end of the Linked Query Tutorial.


LCL007 - Generate an Application

Using a generated application, you can "package" one or more queries into a stand-alone application which you can start without first starting LANSA Client.

This tutorial shows you how to generate a stand-alone application based on the query you created in tutorials LCL005 - Build, Execute and Save a Join Query and LCL006 - Build, Execute and Save a Linked Query. When you have finished this tutorial, you will be able to run the Sample query, the Join query and the Linked query without first starting LANSA Client. If you haven't completed the previous tutorials to create and save the queries used in this tutorial, then any query that you have saved can be used.

These are the steps required to generate and application:

Step 1. Create a New Generated Application

Step 2. Define your Application

Step 3. Add a new menu to the application's menu bar

Step 4. Add Commands to the new Queries Menu

Step 5. Add more Commands to the new Menu

Step 6. Run the Generated Application

Step 7. Distribute a Generated Application


Step 1. Create a New Generated Application

1. Select the Main Window's File menu and choose the Generate command. The Generated Applications dialog box is displayed.

The Generated Applications dialog box shows you a list of existing LANSA Client generated applications. Initially this list will be blank.

2. Select the New button. The Generate Application dialog box is displayed.

Now define the details of your application as described in Step 2. Define your Application


Step 2. Define your Application

In the Generate Application dialog box you define your application's characteristics and select the queries to be included.

1. Give your new application a name, by entering Sample in the Application Name entry box.

2. Give the application an executable name, by entering Sample in the Executable Name entry box. You can use upper and lower case for this name.

Add some menu items as described in Step 3. Add a new menu to the application's menu bar


Step 3. Add a new menu to the application's menu bar

To give you a quick start with your new query, you are provided with a ready to use menu bar containing menus and commands. These are the basic commands, such as File, Print and so on and they are listed in the Generate Application's Menu box when it is opened. You cannot modify these commands.

For each query packaged in your application, you will need to add a command to your menu bar in order to start the query. In this tutorial, these commands will be added to a new item in the menu bar.

1. Add a new menu to the menu bar by placing your mouse pointer on any item in the Menu box and pressing the Add button.

Do you see the additional blank entry that has been added above the Options item in the Menu box? This will become a menu on your application's menu bar.

2. Give the new menu a name, by entering &Queries in the Menu Text entry box.

What you have done is added a menu item called Queries to the menu bar with Q as its short cut key. The '&' character does not appear, but it causes the Q character to be underlined.

Now add some commands to your new menu, as described in Step 4. Add Commands to the new Queries Menu


Step 4. Add Commands to the new Queries Menu

You will need to add one command for each query that you include in your application.

1. To create a new command, highlight the menu item immediately below your new Queries menu item and press the Add button. A new entry will be added above the menu item you highlighted.

2. Give the entry a name, by typing &Simple Query in the Menu Text box.

As Simple Query is to be a subcommand of the Queries menu, you need to indent it beneath the Queries menu in the list.

3. To do this, keep the Simple Query entry selected and then press the [image] right arrow button. This indents the Simple Query entry to the right by one position.

4. While Simple Query is highlighted, select lcsample.lcq (the Sample Query) from the Query To Execute list.

You now have a subcommand in the Queries menu. This command is called Simple Query and has the letter S as its short cut key. When you select this command, it will execute the Sample Query which was used earlier in this tutorial.

Step 5. Add more Commands to the new Menu


Step 5. Add more Commands to the new Menu

Now to add the commands for the Join and Linked queries.

1. Select the Add button again and type &Join Query in the Menu Text box.

a. Indent your new command beneath the Queries menu item by pressing the [image] right arrow button at the bottom of the box.

b. Select the My Tutorial Join Query (Tutorial Join Query) from the Query to Execute list.

2. Select the Add button again and type &Linked Query in the Menu Text box.

a. Indent your new command beneath the Queries menu item pressing the [image] right arrow button at the bottom of the box.

b. Select the TutLink.LCQ (Tutorial Linked Query) from the Query to Execute list.

3. If your commands are not in the correct sequence, re-arrange them using the [image] up and down arrows.

You may wish to attach a different icon to this application. The LANSA Client icon in the top right corner of this dialog box is the icon that will be used unless you replace it.

4. To change the icon, select the Change button and then select an icon from your own files using the browse dialog box.

5. Select the Host to be associated with this application. In this example, the LANSA Client Tutorial (LCL) is the selected partition.

You are now ready to generate the LANSA Client application. Check that you have selected a Query to Execute for each of you new menu items. If you haven't, an error message will be displayed and the Generate will be cancelled.

6. Press the Generate button. LANSA Client will generate the application. Reply Yes to the message telling you that your new applications will be stored in the APPS directory. (There is no option for you to choose the directory.)

If the application has generated successfully, the generated application will be included in your LANSA Client menu.

Now run the application as described in Step 6. Run the Generated Application .


Step 6. Run the Generated Application

1. Start the generated application by selecting the LANSA Client group from the Windows Start menu.

2. Click on the Sample application in the LANSA Client menu.

Note that you do not have to start LANSA Client first.

In the Sample application's Main window, did you notice that LANSA Client has included your new Queries menu in the menu bar?

3. One by one, select the commands in your Queries menu to run the queries.

You may wish to distribute your application as described in Step 7. Distribute a Generated Application.


Step 7. Distribute a Generated Application

You can distribute any generated application to other PCs, provided the PC has LANSA Client installed. By distributing generated applications, you allow others to use your queries without having to build the queries themselves.

When you distribute an application, you must also distribute the queries that are linked within the application.

You have now completed the Application Generation tutorial.


LCL008 - Create a LANSA Client Environment

Bypass this section if the Tutorial partition is included in the list of Hosts to which you can connect.

If you don't have the Tutorial partition included in your list of host systems, it can be added using the LANSA Client Administrator. This can be done on any PC and the environment file (LC.LCX) copied to your PC.

Note: The Administrator does not give you any special authority or access to the data on the host. Access to host data is controlled by the security options in the LANSA Repository. These are independent of LANSA Client.

The LANSA Client Administrator can be accessed in more than one way depending on your site's preference. Refer to the Administrator Tasks in the LANSA Client Administrator Guide for the options available.

The LANSA Client Administrator dialog box is displayed when you start the Administrator.

The LANSA Client Administrator dialog box shows you the hosts to which you could be connected and allows you to specify the options you would prefer to use in your LANSA Client environment.

The options you select on this dialog box apply to LANSA Client in general, rather than when you are connected to a particular host. For further details of these options, refer to the Administrator Tasks in the LANSA Client Administrator Guide.

1. Choose these options for this tutorial:

Option | Setting for this tutorial
Application Generation Support | Tick this box. A tick authorizes you to generate applications.
Saving results: Field Name, Field Label or Field Description | Select the Field Label.
Default Join Type | Select Matched Records.

2. Press the Add Host button to specify the host details as described in Define the Host & its Options.

3. Press the Language button to select the language in which you wish to run LANSA Client. The default is English.

4. Press the Query Location button to specify the location of the query, if it is different to the default (C:\Program Files\LANSA Client V13).

5. You will not use the DateTime Result options during these tutorials, so the defaults may remain unchanged.

5. When you have completed the setting the Tutorial partition details, press the Save button. The options you have defined are written to the LANSA Client configuration file, LC.LCX.

6. Press the Exit button to exit from the Administrator.

If the Administrator, as described in this section, is not running on the same PC as the tutorials, you will need to take a copy of the LC.LCX file and add it to the LANSA Client directory on the PC to be used for the tutorials.

Anyone wishing to access the tutorial, can do so with a copy of this LC.LCX file.

Before you can use a new or changed LANSA Client environment, you must close and start LANSA Client again.


Define the Host & its Options

When you press the Add Host button, the Host Configuration Details dialog box is displayed.

The Host Configuration Details dialog box allows you to define a host system to the LANSA Client environment as well as options relating to the use of that host system.

1. Choose these options for this tutorial:

Option | Setting for this tutorial
Host Name | Enter the name of the host on which the Tutorial Partition has been created.
Host Description | Enter Tutorial Partition or similar name. This is the name that will appear in the Host Selection list.
Host Partition | Enter LCL or other, applicable.
Host Language | If you are using the default language, English (ENG), for the LCL partition, leave the Host Language blank. The other languages available for this partition are French (FRA) and German (DEU).
User and Password | Enter your User ID and Password. If you leave these blank, LANSA Client will use the User ID and Password used by your communications software unless you are using a TCP/IP connection. For TCP/IP, this User ID and Password will be used.
Maximum Records | Leave at 0 for this tutorial. This means that no limit will be placed on the records retrieved from the host.
Advanced Options: |
Allow Inserts/Updates | Select this option as editing data is included in these tutorials.
Allow Deletes | Select this option for the tutorials.
Display Object Names | Select this option. Object name display can also be turned on or off by the user when creating an inquiry.
 | For information about the Advanced Options, refer to the Administrator Tasks in the LANSA Client Administrator Guide.

2. Press the OK button when you are satisfied with your entries.

You are returned to the Administrator dialog box. The Tutorial partition that you have just defined will now be included in the Configured Hosts list in this dialog box.

This is the end of the LANSA Client Tutorial.


Set up the Tutorial files

Before you can use the LANSA Client Tutorial, you need to set up the files that are required.

  • On a IBM i Host
  • On a Windows Host


IBM i Host

Before you can use the LANSA Client Tutorial, the LANSA Client Tutorial partition on the IBM i needs to be created and populated with the data used by the Tutorial.

You can set up these files using the following steps:

  • Step 1. Create a New Library
  • Step 2. Create the New Partition
  • Step 3. Use other Languages (optional).
  • Step 4. Add the LANSA Client Tutorial files

Note that these steps are applicable to an IBM i host only.

Further Information

Ý Set up the Tutorial files


Step 1. Create a New Library

To create a new partition you must sign on as the LANSA security officer.

Use the OS/400 CRTLIB command. Use the prompt key if you are unsure of the parameters.

Library Name: | DC@LCLLIB
Library Type: | *PROD
Description/text | LANSA Client Tutorial

If the @ character is not on your keyboard, you may use the (ALT/HEX) key sequence of "7", then "C".

Step 2. Create the New Partition

Ý IBM i Host


Step 2. Create the New Partition

1. Sign on to LANSA using this LANSA command:

<pgmlib> /LANSA PARTITION(SYS)

2. From the LANSA Main System Menu, choose the Housekeeping Menu.

3. From the Housekeeping Menu, choose the Create or change system partition definitions option. The System Partitions panel is displayed with a list of the existing partitions:

DC@P400402 System Partitions

Select partition to be reviewed, changed or deleted

Or use the ADD command key to add a new partition

Sel Partition Description

_ XXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

_ XXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

F1=Help F3=Exit F6=Add/Create F12=Cancel F14=Messages F17=LastActDtl

4. Select F6, the Add/Create function key, to create a new partition.

5. The Add System Partition screen is displayed. Only the entries which are underlined need to be keyed in or changed.

DC@P400403 XXXXXXX System Partition

Partition identifier : LCL

Partition description : LANSA Client Tutorial

Module library : DCLCLLIB

Unique object prefix : Q

Security officer : QOTHPRDOWN

Default file library : DC@LCLLIB Include in library list ? NO

Initial public access : NORMAL Lib for Help Panel Groups: XXXXXX

Copy system fields from: SYS

SAA/CUA standards apply? YES

Multilingual support ? YES

Help option on menus : Display process or function HELP text

Return prefix on menus : Return to

Exit option on menus : Exit from system

Keep translated RPG ? NO in source file ______ in library _______

Keep translated DDS ? NO in source file ______ in library _______

Configure Task Tracking: NO / NO / NO / NO / NO / NO

Enable for full RDMLX : NO

Enable Documentor : NO

Fnn=Help Fnn=Exit Fnn=Cancel Fnn=Messages Fnn=LastActDtl

Note: For the Unique object prefix choose a character which is unique for your site. This is the only entry that may be different to those shown in this example.

Multilingual Support: Option should be YES, so that when you do the "Import", the German and French definitions are created for you.

6. Press Enter.

Other screens will be displayed for SAA/CUA, frameworks and groups settings. Accept all defaults on these screens.

If you would like more details about the entries on these screens, refer to Creating or Changing Partition Definitions in the LANSA for iSeries User Guide.

After a short wait, you will be returned to the System Partitions list, indicating that the new partition has been created.

7. Press the Cancel function key to return to the Housekeeping Menu.

Step 3. Use other Languages

Ý IBM i Host


Step 3. Use other Languages

You will perform these steps if the LANSA Client tutorials are to be used in a multilingual environment.

Note: If you are not using the multilingual features of LANSA Client, then you do not have to add any other languages.

Although you specified YES for Multilingual Support, it is not fully implemented until you define a language. When you import the Tutorial then English, French and German are automatically set up for you if they do not already exist. Before you import you must define one language, perform these steps.

1. From the Housekeeping Menu, select the Change partition being worked with option. The Systems Partitions screen is displayed with a list of the existing partitions.

2. From the System Partitions list, select the partition you wish to work with (in this case LCL) and press Enter. The Housekeeping Menu will be displayed.

3. Choose the Work with partition multilingual attributes option from the Housekeeping Menu. The Multilingual Partition Languages screen, similar to this example, will be displayed, no languages are yet available:

DC@P400501 Multilingual Partition Languages

Multilingual Partition : LCL

Select language to review/change or use Add key to add a new language

Sel Language Description Default

_ DEU German

_ ENG English YES

_ FRE French

F1=Help F3=Exit F6=Add/Create F12=Cancel F14=Messages

4. Press the Add/Create key to add a new language

or

if you later wish to review/amend a language listed, select the language and press Enter.

The Add or Review/Change Multilingual Partition Language screen will be displayed, as appropriate.

DC@P400502 Add Multilingual Partition Language

Multilingual partition . . . . . : LCL

Language . . . . . . . . . . . . . ENG_

Language description . . . . . . . German___

DBCS support required . . . . . . NO YES, NO

IGCCNV required . . . . . . . . . NO YES, NO

Function key / description . . . 18 / IGCCONVERT

Line number . . . . . . . . . . 22

RLTB support required . . . . . . NO YES, NO

Default language . . . . . . . . . YES YES, NO

Development language . . . . . . . NO YES, NO

F1=Help F3=Exit F12=Cancel F14=Messages

If adding a new language, enter a three or four character identifier representing the new language as well as a description.

5. Press Enter to continue to the Multilingual SAA/CUA Defaults. Amend the text on the panels displayed, if required.

6. Press Enter to continue to the Multilingual Function Key defaults in the language you have selected. The Multilingual Function Key Defaults are spread over 2 panels. Use roll-up to reach the next screen when there is "+" in right bottom corner. Make changes if required.

7. Press Enter. After a short wait, you will be returned to the Multilingual Partition Languages screen where the new language will be listed.

8. To add another new language, select the Add/Create key and repeat these steps, otherwise press the Exit function key to return to the LANSA Main System Menu.

Step 4. Add the LANSA Client Tutorial files

Ý IBM i Host


Step 4. Add the LANSA Client Tutorial files

Once you have successfully created the partition, you use the standard LANSA "IMPORT" facility to load the Tutorial files for LANSA Client from the LANSA for IBM i Installation media.

To do this:

1. Sign on to IBM i using the QSECOFR profile.

2. Sign on to the LANSA PARTITION(LCL) using the following command:

<pgmlib> /LANSA PARTITION(LCL)

3. From the LANSA Main menu, select Work with Housekeeping Tasks.

4. Select Import objects into this partition and specify LCLIENT (in the <pgmlib>) as the save file to import from and submit to batch.

When you receive the successful completion message, review all of the reports produced by the import to make sure that it was completed successfully.

When you are satisfied that the import was successful, you can start using the Tutorial partition.

Further Information

Ý IBM i Host


Windows Host

Before you can use the LANSA Client Tutorial, the LANSA Client Tutorial database needs to be available to your PC.

  • If your PC is a slave to an IBM i system, then you can use the tutorial files on the IBM i if they are set up as described in Set up the Tutorial files on an IBM i Host.
  • If your PC is a slave to a Visual LANSA system on a Windows PC, then you can import the files from the host Visual LANSA system.
  • Alternatively, and regardless of whether your PC is a master or slave Visual LANSA system, you can set up your own tutorial files using the following steps:
  • Step 1. Create the New Partition
  • Step 2. Add the LANSA Client Tutorial files

Note that these steps are applicable to a Windows system only.

Further Information

Ý Set up the Tutorial files


Step 1. Create the New Partition

It isn't compulsory to have a partition called LANSA Client Tutorial, but if you do, it will match the steps in the tutorial. In these steps the LANSA Client Tutorial(LCL) partition is created.

1. Start the Visual LANSA Development Environment. You can start Visual LANSA from the Start menu, and select [image] from the Programs list or you start LANSA using the icon [image] on your desktop. The desktop icon is created as part of the LANSA software installation.

2. Click on LANSA in the list or the icon to open the LANSA folder.

3. From the LANSA folder, click Development Environment to start Visual LANSA. The Visual LANSA Logon dialog will appear.

4. Log on to Visual LANSA. The LANSA Editor will be displayed.

5. From the LANSA Editor's File tab, choose New and then Partition.

The New partition dialog box will be displayed.

6. Enter the following in the New partition dialog box:

Option | Enter
Name | LCL
Description | LANSA Client Tutorial
Unique Prefix | Accept the default
Security officer | Enter the security officer at your location. This is mandatory
Module library | LCLLIB
Default library | LCLLIB
Include in library list Enable For Full RDMLX Open in editor | Accept the defaults.

7. Press Create when the details have been entered. Your new partition will be created. To use this partition, you will need to first exit from Visual LANSA.

If the Create button is greyed out, it could be that the partition already exists, so there is no need for you to create it.

8. Exit from Visual LANSA.

Step 2. Add the LANSA Client Tutorial files

Ý Windows Host


Step 2. Add the LANSA Client Tutorial files

1. Start Visual LANSA again. Your new LCL partition will be displayed in the list of Partitions on the Visual LANSA logon dialog box.

2. When you have entered the password, before you press OK, select the new partition, in this example LCL, and press the Partition Init... button to open the Partition Initialization dialog box.

3. Select the LANSA Client field and file definitions option. Press OK to load the Tutorial files into your new partition. There will be a short delay while the partition definitions are loaded.

Note: If you don't create a new partition, be sure that the LANSA Client field and file definitions option has been selected in the partition in which you will be using for the LANSA Client Tutorials.

Once these steps have been completed, your tutorial files will be ready to use.

Ý Windows Host


Appendix A. Error Message Codes

These error codes are included:

  • LANSA Open Error Codes
  • Error Code 3 - Internal Error Reason Codes
  • Error Code 23 - CPI-C Return Codes


LANSA Open Error Codes

Almost every LANSA Open function returns a True or False value. If a False value is returned, it will be defined by the error codes in this list.

When a function returns FALSE, LceGetStatus can be used to determine the code.

The object in error is included in the error message where applicable. This is represented by %d, %c or %100s in these messages.

Error Code | Reason
0 | The title: Error
1 | The title: LANSA Open Validation Messages
2 | The title: Cancel
3 | Internal Error. Reason Code = %d. Note: The Reason Codes (represented by %d) are listed in Internal Error Reason Codes.
4 | An error was encountered while allocating memory.
5 | Invalid parameter. The parameter cannot be a NULL pointer.
6 | The field %100s has not been defined for the current session.
7 | The file %100s has not been defined for the current session.
8 | The priority %d is not valid. It must be a value in the range 5 to 80.
9 | The heading number is not valid. It must be in the range 1 to 3.
10 | The field %100s cannot be set to the value specified because it exceeds %d, the maximum length for the field.
11 | No Select Options have been specified in the Options list.
12 | The Select Option %.100s is not valid. The only valid Select Options are *STARTKEY, *BACKWARDS, *LOCK, *MAXREC.
13 | The function has not been specified, or it exceeds the maximum length of %d.
14 | No Fields were specified in the Field list.
15 | The parameter %.100s is too long. It exceeds the maximum length of %d.
16 | The combination of parameters is not valid. A program cannot be specified if a process and function are defined. If the process is defined the function must be defined also.
17 | The temporary directory %.100s is not valid. Specify a directory that exists in the Configuration File.
18 | The object %.100s is not defined in the current session.
19 | The numeric %.100s is not in a valid format for the field specified. It should be in the format (%d,%d).
20 | The Session ID %d is not valid. It must be a positive number less than the maximum number of Sessions, 20.
21 | The Session ID %d is not open.
22 | The Session ID %d is already open.
23 | A Communications Error has occurred for the action %.100s. The return code is %d. Note: The return codes (represented by %d) are listed in CPI-C Return Codes.
24 | A memory allocation error occurred while trying to initialize the library. No LANSA Open functions can be called.
25 | The LANSA.INI file is not valid. No LANSA Open functions can be called.
26 | The communications type is not valid. The session could not be opened.
27 | The PC Support Router has not been started. No LANSA Open functions can be called.
28 | Communications Manager has not been started. No LANSA Open functions can be called.
29 | No previous LceRequestSelect was done for this file.
30 | Error in OS400 command.
31 | Error in called program or process/function.
32 | Submit failed.
33 | Commitment control not started.
34 | Field(s) defined in LceUseField function not defined in the LANSA Repository.
35 | File(s) defined in LceUseFile function are not known to the LANSA Repository.
36 | Validation errors have occurred.
37 | I/O errors have occurred.
38 | Unknown error response code received.
39 | The name %.100s is not a valid file name.
40 | The library name in file %.100s is too long.
41 | The file name in file %.100s is too long.
42 | The member name in file %.100s is too long.
43 | The record number %d is not valid.
44 | Record not found.
45 | The maximum number of concurrently Open Sessions, %d, has been reached. A new Session cannot be obtained.
46 | The list passed contains an invalid comma or does not have one where it is necessary.
47 | An internal file cannot be opened. This may be due to insufficient disk space on the temp drive or not enough file handles.
48 | Cannot open the session because the system name, userid and password were not all specified.
49 | An internal file could not be written to. Ensure that there is enough space on the drive which was nominated as the temporary drive or the current drive if one was not specified.
50 | You are not licensed to use this product. Contact your LANSA vendor to obtain a LICENSE.
51 | Your license is valid, however the maximum number of users are already using it.
52 | Failed to load Dynamic Link Library Module, %.100s .
53 | Failed to load Dynamic Link Library Procedure, %.100s .
54 | The option or value specified is not valid.
55 | A key list cannot be specified for LceRequestSelect or LceRequestSelectWhere when blocking options are used.
56 | The conditions for LceRequestSelectWhere exceed the maximum length of %d.
57 | Blocked I/O cannot be used with the *BACKWARDS option.
58 | The Session ID %d must receive all the records via LceReceiveNextX before this function can be used.
59 | The buffer passed to LceReceiveNextX is too small to contain the complete record. An incomplete record has been returned.
60 | LceReceiveSelect cannot be used when the *RECEIVEIMMED option has been specified.
61 | LceReceiveNextX cannot be called unless LceRequestSelect or LceRequestSelectWhere is called with the *RECEIVEIMMED option specified.
62 | No more records to be returned by LceReceiveNextX.
63 | The options *BLOCKBYRRN and *BLOCKBYKEY are not valid when specified together.
64 | LceRequestSelect and LceRequestSelectWhere cannot be called for logical files when blocked I/O is being used.
65 | Invalid Communications Cursor Type specified.
66 | Invalid Communications Type specified.
67 | Invalid Host Type specified.
68 | The variable specified does not exist or a multilingual variable was requested in a non-multilingual partition.
69 | Invalid Help Text Type Specified.
70 | End Of Line string specified for Help Text is too long.
71 | Error when accessing or creating a Local Data Dictionary file. Ensure that there is enough space on the drive which was nominated as the Local Data Dictionary drive or the current drive, if one was not specified.
72 | Invalid Field list.
73 | Host returned an error code on last operation.
74 | Inconsistent Type, Length or Decimals was encountered for field %.100s .
75 | Invalid 3GL parameter number combination. The first level number must be from 0 to 19. The sub-parameter must be from 0 to 255.
76 | Invalid parameter Direction specified.
77 | Invalid parameter Type specified.
78 | Aggregate parameter Length for this first level number exceeds the maximum allowed OR numeric parameters length or decimals is invalid.
79 | Parameter %d, %d has not been defined using Lce3GLDefineParameter.
80 | Invalid attempt to set or get a parameter value which has an inappropriate direction value. Direction must be "P" or "B" to set the value, or "R" or "B" to get the value.
81 | An error occurred executing the host 3GL program.
82 | The sum of the length of the fields requested for use exceeds the host's maximum I/O buffer size of %d.
83 | The operation requested has resulted in a communications transaction to the host which exceeds the hosts receive buffers maximum size of %d.


Error Code 3 - Internal Error Reason Codes

This error code is returned when the internal logic of the software detects a condition which should not occur in normal operation.

Specific cause information is not available, however, a Reason Code representing an internal macro is returned. This code may assist in determining the cause of the error.

Listed below are the Reason Codes and the associated internal macro names.

Reason Code | Associated Macro Name
1 | LCE_INTERR_INVALID_DATA_TYPE
2 | LCE_INTERR_DBCS_TARGET_TOO_SMALL
3 | LCE_INTERR_TOO_MANY_DIGITS
4 | LCE_INTERR_TOO_MANY_DECIMALS
5 | LCE_INTERR_NUMERIC_TARGET_TOO_SMALL
6 | LCE_INTERR_RECV_BUFF_TOO_SMALL
7 | LCE_INTERR_RECV_NO_TERMINATOR
8 | LCE_INTERR_RECV_INVALID_COMMAND
9 | LCE_INTERR_RECV_FATAL_ERROR
10 | LCE_INTERR_RECV_BAD_FORMAT
11 | LCE_INTERR_RECV_INVALID_IDENTIFIER
12 | LCE_INTERR_REVERSE_BUFF_TOO_SMALL
13 | LCE_INTERR_COMMS_ALREADY_ALLOCATED
14 | LCE_INTERR_UNKNOWN_ACTION
15 | LCE_INTERR_COMMS_RECV_BAD_DATA
16 | LCE_INTERR_BUFF_TOO_MANY_FILES
17 | LCE_INTERR_BUFF_FILE_READ
18 | LCE_INTERR_BUFF_FILE_ACCESS
19 | LCE_INTERR_RECV_INVALID_FILE_TYPE
20 | LCE_INTERR_DBCS_BAD_CLIENT_STRING
21 | LCE_INTERR_DBCS_BAD_HOST_STRING
22 | LCE_INTERR_PACKED_BUFF_TOO_SMALL
23 | LCE_INTERR_SIGNED_BUFF_TOO_SMALL
24 | LCE_INTERR_TABLE_BUFF_TOO_SMALL
25 | LCE_INTERR_TP_END_SESSION
26 | LCE_INTERR_INVALID_TABLE_INDEX
27 | LCE_INTERR_INVALID_TABLE_RECORD
28 | LCE_INTERR_EDIT_BAD_FORMAT
29 | LCE_INTERR_VARIABLE_BAD_TYPE
30 | LCE_INTERR_DEFAULT_BAD_TYPE
31 | LCE_INTERR_UNKNOWN_RC_LCLCE
32 | LCE_INTERR_PARBLOCK_ZERO_MEM


Error Code 23 - CPI-C Return Codes

This list includes the most common return codes issued with a communications error code (Lce0023) returned by LANSA Open when using the CPI-C communication method (i.e. MS Host Integration Server, Network Services for DOS).

Some of the return codes associated with the allocation of a conversation have the suffix RETRY or NO_RETRY in their name:

  • RETRY means that the condition indicated by the return code may not be permanent and the program can try to allocate the conversation again. Whether or not the retry attempt succeeds depends on the duration of the condition.
  • NO_RETRY indicates that the condition is probably permanent.

Please note that LANSA Open always returns the error codes in hexadecimal format. This documentation lists the error codes in decimal format, so you need to convert the returned code to decimal format before looking up the error in this table.

Dec Value | Description | Reason for error
6 | SECURITY_NOT_VALID | Userid/password combination invalid (i.e. user profile has not been defined in the host or this is an incorrect password for the userid - note that passwords are case sensitive for Windows, Linux and non-IBM i hosts). No job could be started in the Host, so don't look for a job log.
17 | DEALLOCATED_ABEND | 1. LANSA program library not in user's job description's initial library list. 2. When I/O module was compiled, settings *IOMXSERVER, *IOMBLOCKBYKEY and *IOMBLOCKBYRRN were missing in DC@OSVEROP data area. (Refer to MCH0802 in the job log). 3. Other causes may be discovered in the IBM i Job Log or Windows Application Event Log.
18 | DEALLOCATED_NORMAL | 1. Partition doesn't exist or user is not authorized for the partition. 2. File doesn't exist or the user is not authorized for the file. 3. Language used is not defined in the partition. 4. Other causes may be discovered in the IBM i Job Log or Windows Application Event Log.
19 | PARAMETER_ ERROR | On the Host Integration Server, use SNA Server Admin. to add a LU6.2 partner relationship between the "Local LU" and "Partner LU" which has a MODE of "BLANK".
20 | PRODUCT_ SPECIFIC_ERROR | As for Error 19 (PARAMETER_ERROR).
24 | PROGRAM_ PARAMETER-CHECK | Most likely cause is that the userid or password is more than 8 characters. SNA requires userids and passwords to be a maximum of 8 characters.
26 | RESOURCE_ FAILURE_NO_ RETRY | Most likely cause is hardware failure. Check the connections (cabling) between PC and host.
27 | RESOURCE_ FAILURE_RETRY | PC has run out of memory. Confirm by opening a session with no declarations. Reduce the number of applications loaded when the system starts.


Disclaimer

While every effort has been made to ensure that the information in the LANSA documentation is accurate. In no event shall LANSA be liable for any damages arising from its use.

LANSA MAKES NO WARRANTIES, EXPRESSED OR IMPLIED.

Copyright and trademark information

© LANSA. "LANSA" is a trade name and registered trademark of LANSA in the United States of America, Australia, the United Kingdom and other countries. All rights reserved.

The following are some companies which require attribution:

Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both. Microsoft, Windows, .NET and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

This material may contain other proprietary notices and copyright information, the terms of which must be observed and followed.