Page History
...
See also 7.109.2 SELECT_SQL Column Names versus Column Values
|
|---|
Specifies the columns (fields) and their associated "SQL source" or function.
...
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.
...
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:
...
For further details, refer to the IBM manual DB2 UDB for IBM SQL Reference.
|
|---|
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.
...
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.
...