WebFOCUS Techniques

Put Values Contained in Two Fields into One Field

Sometimes it is necessary to merge 2 separate fields into one field. The most common example of this is first name and last name. If a letter is to be sent to an individual, it is necessary to put at least the first name and the last name into a field that contains both so that the name and address block of the letter can be constructed. WebFOCUS allows this to be accomplished with concatenation operators. There are two kinds of concatenation operators: one that preserves the blanks at the end of the merged field (weak concatenation) and one that eliminates the blanks at the end of the merged field (strong concatenation). Both are detailed below.

Weak concatenation: The | operator will perform weak concatenation between 2 fields (all trailing blanks are retained). For example, if SPRIDEN_FIRST_NAME is to be concatenated with SPRIDEN_LAST_NAME into one new field called FULLNAME, we would go to the COMPUTE tab in Report Assistant and create a new COMPUTE named FULLNAME and specify it as a alphanumeric with a length of 75.

We determined the length of FULLNAME to be 75 characters; there is a maximum allowable characters of 15 in SPRIDEN_FIRST_NAME and a maximum allowable characters of 60 in SPRIDEN_LAST_NAME.

The code to concatenate the fields would be:

SPRIDEN_FIRST_NAME | SPRIDEN_LAST_NAME

Since the first name is specified as an alphanumeric with a length of 15 and the last name is specified as an alphanumeric with a length of 60, this would result in the following, if the first name was Jonathon and the last name was Smith: Jonathon Smith

In this example, it is undesirable to retain all of the spacing between the first name and the last name, so strong concatenation is needed. There will be other times that weak concatenation is desirable and this technique can be used in those cases.

Strong concatenation: The || operator will perform strong concatenation between 2 fields (all trailing blanks are eliminated). For example, if SPRIDEN_FIRST_NAME is to be concatenated with SPRIDEN_LAST_NAME into one field named FULLNAME, we would go to the COMPUTE tab in Report Assistant and create a new COMPUTE named FULLNAME and specify it as a alphanumeric with a length of 75. The code would be:

SPRIDEN_FIRST_NAME || SPRIDEN_LAST_NAME

Because we have specified strong concatenation, this would result in the following, if the first name was Jonathon and the last name was Smith: JonathonSmith

Now, we have eliminated all spaces between the first and last name. In this example, it is undesirable to eliminate all of the spacing between first name and last name, one space is needed between the 2 fields. There will be other times that strong concatenation is desirable and this technique can be used in those cases.

Solution: To remove all trailing blanks and show first name, then last name, with a space residing between the two, the code would be:

SPRIDEN_FIRST_NAME || (' ' | SPRIDEN_LAST_NAME)

By using a combination of the techniques (strongly concatenate a blank and the last name to first name), we get the desired result: Jonathon Smith.

In the above example 76 characters was used. This allows for maximum possible characters; 15 characters for SPRIDEN_FIRST_NAME, 60 CHARACTERS for SPRIDEN_LAST_NAME and the additional character is used for the space between SPRIDEN_FIRST_NAME and SPRIDEN_LAST_NAME.

MCIS Documentation and Information page at Miami University