Programming with Reflection

image\rwnprg32.gif SetSQLStatement method

 

Syntax object.SetSQLStatement Type, Statement

 

Description

Sets the portion of the current SQL query specified by the Type argument. Use additional SetSQLStatement lines to set multiple conditions using different query options. The completed SQL statement determines which data values are transferred from the host. It affects AS/400 data transfer only, and is only relevant during 5250 sessions. You can use this command in conjunction with AppendSQLStatement to create SQL statements of any length.

Arguments

Type Argument type: Enumeration
Specifies an SQL query type. Possible values are:

 rcAS400Select

 Use this value when you want the Statement string to determine which fields to transfer. For example to transfer the fields named LAST, FIRST and ID:

 .SetSQLStatement rcAS400Select, "LAST, FIRST, ID"

 To transfer the sum of the values in the COST field:

 .SetSQLStatement rcAS400Select, "SUM(COST)"

 

 rcAS400Where

 Use this value when you want the Statement string to set one or more conditions that determine whether a record is transferred. For example, to transfer the records whose value for hours is 35:

 .SetSQLStatement rcAS400Where, "HOURS = 35"

 The available tests are =, <, >, <=, >=, <>, NOT, LIKE, IN, BETWEEN, IS, ISNOT, and SUBSTR.

 rcAS400OrderBy

 Use this value when you want the Statement string to determine how to sort selected records. The fields used in this statement must be selected fields.

 For example, to order the output by the LAST field:

 .SetSQLStatement rcAS400OrderBy, "LAST"

 To order the output in descending order based on the value of the PAYRATE field:

 .SetSQLStatement rcAS400OrderBy, "PAYRATE DESC"

 

 rcAS400Having

 Use this value when you want the Statement string to impose a condition on a function of the Select statement. To use a Having condition, you must also create a Select condition. The Statement string should use the following syntax:

 <function>(field name)<test><value>

 

 rcAS400GroupBy

 Use this value to when you want the Statement string to determine how to group returned data after a requested function is performed. A Group By statement is necessary when a function and multiple fields are specified in a Select statement. Use AppendSQLStatement to build the complete SQL statement. The Statement string should use the following syntax:

 <field name1>,<field name2>,<field name3>

 

 rcAS400JoinBy

 Use this value when you want the Statement string to determine how to combine data from multiple files. You can only use a Join By statement if multiple files or members are selected. The Statement string should use the following syntax:

 <field name1><test><field name2>

 

Statement Argument type: String
A string defining the search condition specified by Type. Information about the syntax for this string is given above for each statement type. The maximum string length is 128 characters. You can use AppendSQLStatement to add additional length to any portion of the SQL statement.

Note: Transfer options set using this method supersede any transfer options entered using the following properties. If you create an .xto file, the file will use the transfer options determined by the SetSQLStatement method.

Xfr400SelectString property

Xfr400WhereString property

Xfr400OrderByString property

Xfr400HavingString property

Xfr400JoinByString property

Xfr400GroupByString property

 

 

image\jump.gif Example

image\jump.gif Keyword Index

image\popup.gif Related Topics

image\popup.gif Reflection products that use this command