7.85.1 SELECT_SQL Parameters
See also 7.85.2 SELECT_SQL Column Names versus Column Values
Specifies the columns (fields) and their associated "SQL source" or function.
Fields of type BLOB and CLOB are not supported in the SELECT_SQL command. If one is specified a fatal error will occur when the command is compiled.
All columns nominated by this parameter must be defined in the current function or in the LANSA data dictionary as valid RDML variables.
For each column specified an optional field "SQL source" may be nominated. This field has a maximum length of 50 characters.
When the source is not specified, the default value of *SAME (same as column name) is used. This means that the column name in the function and its "source" in the SQL table are assumed to be the same. When this value is used the column must be defined as a valid real column in one (or more) of the tables nominated in the FROM_FILES parameter.
For example:
SELECT_SQL FIELDS((#CUSTNAM)) FROM_FILES(CUSTMST)
indicates that the column named CUSTNAM is to be extracted from the table CUSTMST and its value returned into the RDML function into the field called #CUSTNAM. This example uses the *SAME default. But the example:
SELECT_SQL FIELDS((#CUSTNO CUSTNAM)) FROM_FILES(CUSTMST)
indicates that column named CUSTNAM is to be extracted from the table CUSTMST and its value returned into the RDML function into the field called #CUSTNO.
And, the further example:
SELECT_SQL FIELDS((#SHORTNAME 'SUBSTR(CUSTNAM,3,10)'))
indicates that a substring of column CUSTNAM (from the SQL table) is to be returned into the RDML function field #SHORTNAME.
And, another example where two files are being joined and the column CUSTNAM is in both tables a correlation is used to clarify which table, CUSTMST or CUSTMST2 to obtain the data from:
SELECT_SQL FIELDS((#CUSTNAM 'A.CUSTNAM')) FROM_FILES((CUSTMST A)(CUSTMST2 B)) WHERE('A.CUSTID = B.CUSTID')
And, the final example:
SELECT_SQL FIELDS(#DEPTMENT (#VALUE1 'AVG(SALARY)') (#VALUE2 'SUM(SALARY)')(#VALUE3 'MAX(SALARY)')
indicates that SQL table column DEPTMENT is to be returned into RDML variable #DEPTMENT, the average of SQL table field SALARY is to be returned into RDML variable #VALUE1, the total into #VALUE2, and the maximum into #VALUE3.
Refer to Specifying File Names.
Note: Up to 20 file (table) names can be specified for use by this command.
Note: When accessing Other Files that are in other databases LANSA locates the database connection information that was used to load the Other File into LANSA. This can be further refined by using the DEFINE_DB_SERVER and CONNECT_FILE BIFs
For each file name specified an optional field "correlation" may be nominated.
When the correlation is not specified, the default of *SAME (IE same as file name) is assumed. This means that when referring to a column in a specific table the actual table name must be used. If a correlation name is used the correlation name must be used to identify a column from a specific table.
Examples
. . . FROM_FILES((ORDLIN) (ORDDTL)) WHERE('ORDLIN.CUSTNO = ORDDTL.CUSTNO')
If correlations were used this statement could written as:
. . . FROM_FILES((ORDLIN A) (ORDDTL B)) WHERE('A.CUSTNO = B.CUSTNO')
|
Portability Considerations |
Visual LANSA does not use @#$ in table names. This conversion is done for the FROM_FILES parameter, but not for table names in the other parameters, e.g. the WHERE parameter. So, in order that the SQL can work on all LANSA platforms, correlations should always be used as in the second example above. Visual LANSA provides access to multiple databases using Visual LANSA Other Files.Visual LANSA Other files can be used in SELECT_SQL, but they must all be from the same database. If aVisual LANSA Other File is in the same database as a LANSA file, then the two can files can be used in the same SELECT_SQL command |
You must enclose the SQL_SELECT WHERE clause in quotes as shown here:
. . . WHERE('EMPNO < ''A9999''')
. . . WHERE('NOT EMPNO LIKE ''%a''')
The where clause may contain either LANSA field names, or column names. (Refer to the FIELDS Parameter for more details.)
The SQL language uses double quotes to surround identifiers that might otherwise be interpreted as SQL syntax. LANSA leaves the contents of double-quoted text untouched. Note, this is the double-quote character ("), not two single quotes ('').
|
Portability Considerations |
LANSA field names in the WHERE parameter will be generated as double-quoted column names into the SQL statement, as long as the field is recognized as being from one of the files in the FROM_FILES parameter. Note that the column name is not always the same as the field name. This is often the case for fields on Other Files, but also happens for certain field names on LANSA files. Refer to Convert Special Characters in Field Names. An exception to the previous paragraph is when an unquoted LANSA field name conflicts with SQL keywords. In this case the field name is NOT converted. Refer to for more details. If selecting from two or more files that have the same LANSA field, the column name may differ between the files. Refer to Convert Special Characters in Field Names. In this case, you, the developer, have two way to control the SQL WHERE clause that LANSA generates. The first is to use a correlation (refer to the FROM_FILES parameter) so that LANSA renames it according to the rules of that file. The second is to use the column name and enclose it in double quotes. If using field names in the WHERE parameter, it is recommended that you leave space around the field name so that LANSA can recognise the field names and convert them appropriately. For example, WHERE( 'a=b') should be instead be WHERE ( 'a = b' ). |
Visual LANSA allows a single field name to be specified instead as shown here:
CHANGE FIELDS(#SELECTION) TO('STATE = ''NSW''')
SELECT_SQL FIELDS(#STATE) FROM_FILES((#STATES)) WHERE(#SELECTION)
DISPLAY FIELDS(#STATE)
ENDSELECT
The contents of the field are used as the WHERE clause and the following needs to be considered:
- A build warning will be generated if a single field name is used in LANSA for i RPG Functions. An error will occur at execution time. Code using this facility can be conditioned so that it is not executed in this environment. See the *OPNQRYF command for an alternate programming method and how to write portable code
- The field name STATE is not preceded by a # (hash) symbol in this example. This is because the actual selection request is being made to the SQL database, not to LANSA. That is, the actual column name must be used. Visual LANSA renames column names that contain @, # or $ and replaces them with an underscore in Naming level 0 files, but this does not occur on LANSA for i, thus the code is portable provided that the LANSA field name is used, not the actual column name. E.g. if the Field is CUST$NAM then this should be used, not CUST_NAM. Visual LANSA will change CUST$NAM to CUST_NAM at runtime (Naming Level 0 file). LANSA for i will leave it as it is.
- Variable comparison values as in :KARTIC will not be replaced. Instead the value must be concatenated into the #SELECTION field.
WHERE clause hints
When searching for data using the like condition, characters with special meaning to SQL need to be escaped if they need to be taken literally. For example, the character '_' matches any character. To literally match '_' then the following syntax needs to be used. This will find all states that start with 'B_':
CHANGE FIELDS(#SELECTION) TO('STATE LIKE ''B!_%'' ESCAPE ''!''')
Note: This nominates the exclamation mark as the escape character. Any "normal" character not greater than 127 in the ASCII table can be used. (Characters %,_,[ do not work on all DBMS systems and so are not recommended.)
This has been tested on ASA, DB2400, SQL Server, and Oracle. The only exception is MS Access, where instead you need to use [] around the character to be escaped. For example: WHERE STATE LIKE 'B[_]%'
Using a Field for Variable Comparison Values
Place a colon (:) immediately in front of the field name, without any spaces separating the colon from the field name to indicate that the name in the WHERE clause should be used. (If the field name is more than six characters long, you will get unpredictable results in LANSA for i RPG Functions.)
|
Portability Considerations |
Fields used to contain variable comparison values in the WHERE clause are not translated by LANSA. Therefore, when using fields in this way, your field names must be six (6) characters or less. (This is because fields are used with their actual name in the generated RPG on LANSA for i.) LANSA for i RPG Functions allow a space between the colon and the field name, but this does not work in generated C code. For portability do not leave a space between the colon and the field name. |
If your code will run in a LANSA for i RPG function, and you have field names that are longer than six characters that are to be used in the WHERE clause, you will need to define a work field for these names with a name that is six characters or less, as in the example below.
Example:DEFINE #KARTIC REFFLD (#ARTICO)
DEFINE #KADTRG REFFLD (#MADTRG)
. . . WHERE ('ARTICO = :KARTIC AND MADTRG > :KADTRG')
For further details about specifying conditions, refer to Specifying Conditions and Expressions For further information about the structure of this clause, refer to the SQL guides.
RDMLX IBM i Other Files with Unicode Fields
SQL on IBM i cannot compare a graphic unicode field directly to a string literal or a character column; a conversion error occurs.
There are two ways of converting the expression to Unicode to avoid the conversion error:
1. Use a LANSA field for comparison. For example, WHERE('MYUNIGRPH = :STD_TEXT').
2. Pass the literal as a Unicode (UX'ssss') literal. For example, instead of:
WHERE('UNIFLD LIKE 'C%')
try
WHERE('UNIFLD LIKE UX''00430025''').
For further details, refer to the IBM manual
Is used to find the characteristics of groups of rows rather than individual rows. Grouping does not mean sorting. Grouping puts each selected row in a group which SQL processes to derive characteristics of the group.
Specify the column(s) you want to group the selected rows by. If more than one column is specified, commas must be used to separate the data. For example, GROUP_BY('EMPTSYEAR, EMPTSWEEK').
Is used to specify a search condition for the groups selected based on a GROUP_BY clause. The HAVING parameter says that you want only those groups that satisfy the condition in the clause. That is, the HAVING clause tests the properties of each group not the properties of the individual rows in the group.
The HAVING clause can contain the same kind of search condition that can be used in the WHERE parameter.
Use this parameter to specify the order you want the selected rows retrieved. The order by parameter can be used the same way as the GROUP_BY parameter.
Specify the name of the column or columns SQL should use when retrieving the rows in a column. If more than one column is specified, commas must be used to seperate the data. For example, ORDER_BY('SURNAME, GIVENAME').
Specify *YES to this parameter if duplicate rows are not required in the result of the SELECT_SQL.
Specify *NO if duplicate rows are required in the result table.
Specifies the name of a field that is to receive the "return code" that results from the I/O operation.
If the default value of *STATUS is used the return code is placed into a special field called #IO$STS which can be referenced in the RDML program just like any other field.
If a user field is nominated to receive the I/O return code it must be alphanumeric with a length of 2. Even if a user field is nominated the special field #IO$STS is still updated.
Refer to I/O Command Return Codes Table for values.
Specifies what action is to be taken if an I/O error occurs when the command is executed.
An I/O error is considered to be a "fatal" error. Some examples are file not found, file is damaged, file cannot be allocated. These types of errors stop the function from performing any processing at all with the file involved.
If the default value of *ABORT is used the function will abort with error message(s) that indicate the nature of the I/O error.
*NEXT indicates that control should be passed to the next command.
*RETURN specifies that in a program mainline control is to be returned to the caller and in a subroutine control is to be returned to the caller routine or the program mainline.
If none of the previous values are used you must nominate a valid command label to which control should be passed.