Advanced SQL Request Editing

The Advanced SQL Request Editing options allow you to extract specific information from host files.

If you are using more than one fieldname in the SQL clause, you must prefix each field name with the following counter: T#. For example, if you need the average value of three fields, enter the commands as follows:

AVG T1.SRCDTA

AVG T2. SRCDTA

AVG T3. SRCDTA

This format applies to all the SQL clauses (fields) in the Advanced SQL Request Editing dialog box. To use the Advanced SQL Request Editing options, follow the steps below.

Type which database fields to download to the PC and which functions to use to create a summary of records in the SELECT field. If the SELECT field is left blank, all fields are downloaded from the host file. You can specify more than one field to download by separating each field with a comma. Add one or more spaces to improve readability.

When downloading a summary of records, the SELECT field uses the following format:

FUNCTION (FIELDNAME)

The FUNCTION can be any one of the following:

Function	Meaning
AVG	Transfers the average value of the specified field for each record specified in the parentheses. This function can only be used for numeric fields.
MIN	Transfers the minimum or lowest value of the specified field for each record group.
MAX	Transfers the maximum or highest value of the specified field for each record group.
SUM	Transfers the total value of the fields for each record group. This function can only be used for numeric fields.
COUNT (*)	Transfers the total number of records that satisfy the WHERE selection criteria for each record group. The COUNT function must always have the asterisk wildcard specified as the FIELDNAME.

The FROM field should contain the host files or libraries you specified in the File Transfer dialog box. If you want to add more files or libraries, you can only do so in the File Transfer dialog box. If more than one file appears in this box, the JOIN BY field will be activated.

Type one or more conditions that the record must meet for it to be downloaded in the WHERE field.

If WHERE is blank, all the records in the specified file member(s) are downloaded. The FIELDNAME TEST VALUE condition specifies the test to be applied to the records in the file member, and only those records that pass the test are downloaded.

•

FIELDNAME is the field or substring of a field in the record format.

•

TEST is the comparison to apply to the field or substring.

•

VALUE is the name of a field, a number or a character constant.

The following table explains how to define these parameters.

Parameter

Description

FIELDNAME

You can enter the fieldname without modifications or apply functions to define the fieldname. You can apply the following functions to fieldnames:

CHAR

Returns a date and time field as a character string. You can specify the USA Standard or Japanese Industrial Standard to the format.

CURRENT

Returns the current date, timestamp, time, or time zone.

DIGITS

Returns the character string for a numeric field.

VALUE

Returns the first non-null value in a list of numeric fields. Separate the fieldnames in the list by spaces and enclose them in parentheses. If all fields are null, the expression results in a null value.

SUBSTRING

Returns a section of a character string. To define this section, you must provide the starting point of the character string in the field and its length.

DATE, TIME, and TIMESTAMP

Respectively returns the date, time, and timestamp of the time-stamp field.

YEAR, DAYS, MONTH, and DAY

Respectively returns the year, the day of the year (from 1 to 365), the month, and the day of the month of the date or time stamp field.

HOUR, MINUTE, SECOND, and MICROSECOND

These functions respectively return the hour, the time, the seconds, and microseconds of the time field or time-stamp field.

TEST

You can use the following mathematical expressions as a test:

= equal

>= greater to or equal

<= smaller than or equal

<> different from

> greater than

< smaller than

•

The BETWEEN x AND y test allows you to specify a range in which the FIELDNAME must be located or two values to which the fieldname must be equal. Value x and value y can be numeric constants or character string constants and value x must be smaller than value y. The constants must be of a type identical to the FIELDNAME.

•

The LIKE ’string’ tests if the FIELDNAME is similar to the specified character string. When using the LIKE test, you can enter standard characters and wildcards. These wildcards are the percentage character (%) and the underscore character (_). Both wildcards can be used in conjunctions. The % (percentage) character accepts a character string of zero or more characters. It can be used with the underscore character. The _ (underscore) character accepts any one character. It can be use in conjunction with the percentage character. Here are some examples of the use of the wildcard characters for the LIKE test.

♦

S% accepts any character string that starts with an S.

♦

%eve takes any character string that ends in "EVE" such as EVE or STEVE.

♦

S%E accepts any character string that starts with an S and that ends with an E such as STEVE or SIMONE.

♦

M_ accepts any two character strings that start with M.

♦

S_E% selects any character string that starts with the letter S and has an E as the third character such as STEVE, STEVEN, and STEPHAN.

•

The IS NULL test selects a record if the value of the fieldname is null. The IS NOT NULL test does just the opposite. It selects the record if the value is not null.

•

IN tests if the field value is equal to one of the values specified in the constant list. The constant list, made up of character string constants or numeric constants separated by spaces, must be enclosed in parentheses. You can specify up to 100 constants of the same type as the FIELDNAME in the constant list.

Examples:

•

IN (12 15 19) returns any record whose field-name value is 12, 15, or 19. IN (’WAYNE’ ’GRAYSON’ ’TODD’ ’DRAKE’) returns all the fields with the name specified in the parentheses.

VALUE

You can enter character strings as values in a test such as in the LIKE or IN tests. When a character string is shorter than the specified field, the string extends to the value length by adding blanks on the right of the value. When a character string is longer than the specified field, the string is truncated.

Character string values are case sensitive and are evaluated according to the exact characters specified; consequently, when the value is in lowercase, only the lowercase character returns.

You can enter character string values that cannot be represented in standard ASCII format in EBCDIC hexadecimal format using the X function.

You must enter all character strings between apostrophes. If you want to insert an apostrophe in the character string, you must enter two apostrophes. For example, ’O’’Donnell’. The Transfer application only supports the right-apostrophe.

A numeric value can include an optional sign (+ or -) and an optional decimal point represented by a comma or a period. You can also specify a mathematical expression using the four basic arithmetic operations:

•

Addition

•

Subtraction

•

Multiplication

•

Division

•

Parentheses

For example, you can download all records where the Stock is greater than the Order by 250 using STOCK > ORDER + 250. Mathematical expressions follow the standard mathematical priorities:

•

multiplication and division first

•

addition and subtraction second

Consequently, the expression 2+5*2 equals 12 not 14. For the same expression to equal 14, it should be stated as (2+5)*2. For example, if you want to download records where the Stock is greater by 250 units than twice the Order, you will use this expression STOCK > 250+ ORDER *2.

Type the commands that specify how to link or join the records of the specified files. The JOIN BY field becomes active when you specify more than one file in the FROM field. You must join each file identified as the sender with at least one other sender file.

You can specify one or more conditions to indicate the similarities between the two files. This allows you to identify which record of one file joins to the records of the other.

You must type the field names that join the files using the following formula: FieldName CONDITION FieldName

•

When you specify a field, you can only join the two numeric fields together. However, the field lengths and types do not need to be identical.

•

You can only join a character field with a character field. However, the lengths do not need to be identical. The CONDITION can be one of the following:

= equal

>= greater to or equal

<= smaller than or equal

<> different from

> greater than

< smaller than

To join three or more files, or to join two files based on two or more common fields, you must use two or more link conditions. To specify several join conditions, separate them with the AND statement. You can use this technique to specify up to 32 join conditions.

Type the sequence in which records are to be sorted in the ORDER BY field. You can sort records by the fields in ascending or descending order. The default sorting order is ascending.

•

Ascending (ASC) sorts from low to high.

•

Descending (DESC) sorts from high to low. SQL sorts the records according to the first sorting fieldname. If two records have similar ORDER BY values, SQL sorts these records according to the second sorting fieldname. This logic applies as long as there are sorting fieldnames specified. Null values are considered as the last values regardless of sort order. You must also specify field names or functions specified in the ORDER BY field in the SELECT field. You must specify the sorting order in one of the following ways:

♦

FieldName1 [ASC|DESC] [ABS],

♦

function(FieldName2) [ASC|DESC] [ABS],

function is one of the functions available for the SELECT field. You can also specify a numeric sort according to absolute values (ABS) by adding a space and the ABS sorting order after the fieldname. For negative values, the ActiveX control ignores the negative sign and uses the absolute value instead.

Click the Group Options button to use the GROUP BY and HAVING fields. If you don't want to use the Group fields, click the Clear Group Options field.

Type the commands separate records from one or more AS/400 files and group them into two or more groups in the GROUP BY field. Select one or more fields specified in the SELECT field to group the host records. SQL groups the records in the order you entered the fields in the GROUP BY field. You can specify up to 50 fieldnames as long as they are separated with commas. You also can enter blanks to improve readability.

Type the commands that allow you to pinpoint the download transfer request that summarizes groups of records in the HAVING field. While you can use the WHERE field to review all records in a group, typing TEST and VALUE in the HAVING field allows you to use only the records that contain summary information about each group. A condition stated in this field appears in this format:

•

Function (FieldName) test value

♦

Function is one of the functions available in the SELECT field

♦

FieldName is a field defined in the record format specified in the FROM field but does not have to be selected in the SELECT field

♦

Test is the type of comparison to apply to the function

♦

Value is a function on a field, a number, or character string constant as defined in the WHERE option.

When defining the test for the HAVING field, you can use the following mathematical expressions as logical comparators:

= equal

>= greater to or equal

<= smaller than or equal

<> different from

> greater than

< smaller than

You can also use the logical AND and the logical OR to combine multiple tests up to a maximum of 50 tests.

Type your download method in the SELECT field. There are four methods available:

•

Entire File — downloads data for every record in the file. This is the easiest way to download a file. BlueZone Access Server uses this method by default.

•

Part of a File — processes the data file to extract only the records that you selected using the selection options.

•

From Several Files — downloads information from many files that contain similar types of data. You can also sort information to extract only a portion of what the files contain.

•

Summary of Record Groups — separates records into two or more groups and summarizes these groups into one record. You can then download this summary of group records.

When the fields from more than one file in the GROUP BY option are the same as in the SELECT option, your target file will be a summary of record groups.

The following table lists which options are available and/or necessary for each transfer method.

	SELECT	SELECT	ORDER BY	ORDER BY	GROUP BY	HAVING
Entire file	0	—	X	—	—	—
Part of a file	0	0	X	—	—	—
Several files	0	0	X	0	—	—
Summary of record groups	0	X	X	X	X	X
Legend: 0 = Required parameter X = Optional parameter