8.19.1 SELECT_SQL Free Format Parameters
Specifies the fields that will receive the result of the SQL command specified in the USING parameter. This parameter can only contain fields, groups and expandable groups
All columns nominated by this parameter must be defined in the current function or in the LANSA data dictionary as valid RDML variables.
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.
The SQL_SELECT USING parameter can be any valid enhanced expression . It is best to use single quotes to delimit strings so that double quotes can be used around the SQL identifiers. This means that single quotes around string literals must be doubled up. The first and second examples have the same result except that the last single quote to terminate the SQL literal is specified in two different ways - either '''' or "'". The fourth example puts the value and the quotes into a work field which may be the easiest method to read and maintain.
. . . USING('SELECT "EMPNO" FROM "XDEMOLIB"."PSLMST" WHERE "EMPNO" < ''' + #EMPNO + '''')
. . . USING('SELECT "EMPNO" FROM "XDEMOLIB"."PSLMST" WHERE "EMPNO" < ''' + #EMPNO + "'")
. . . USING('SELECT "EMPNO" FROM "XDEMOLIB"."PSLMST" WHERE NOT "EMPNO" LIKE ''%a''')
. . . #STD_TEXT := "'%a'"
. . . USING('SELECT "EMPNO" FROM "XDEMOLIB"."PSLMST" WHERE NOT "EMPNO" LIKE ' + #STD_TEXT)
The SQL language uses double quotes (the quote character may differ on some databases) to surround identifiers that might otherwise be interpreted as SQL syntax By quoting identifiers you are assured that the identifiers will not clash with any SQL syntax on any database.
The USING parameter does not support embedded fields (e.g. :KARTIC) like the WHERE parameter does.
|
Portability Considerations |
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 a Visual LANSA Other File is in the same database as a LANSA file, then the two files can be used in the same SELECT_SQL command SQL Table names may differ from the LANSA file name, for example when an @, # or $ is in the name. This name may also be different between different operating systems. If the SQL Command is intended to be executed on multiple platforms ensure that the table names are either the same or they are specified as a variable in the USING parmater |
USING clause hints
It is usually necessary to specify the collection that the table is in. This is not necessary in the WHERE parameter because LANSA parses the SQL and determines the correct collection to use.
All the identifiers must be spelt exactly as required by the database. For example the LANSA name for an Other File may be different to the actual table name if the table name already exists or its longer than 10 characters. It's the table name that must be specified. The following example uses a table named "Long Table Name With Spaces" with columns named "Long Field Name 1" and "Long Field Name 2". LANSA has loaded the table as "LONG_TABLE" and named the fields "LONG_FIEL" and "LONG_FIE1".
SELECT_SQL FIELDS(#LONG_FIEL #LONG_FIE1) FROM_FILES((LONG_TABLE)) IO_ERROR(*NEXT) USING('select "Long Field Name 1", "Long Field Name 2" FROM LX_DTA."Long Table Name With Spaces"')
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(#TABLE) TO('SELECT "CUSTNUM" FROM "XDEMOLIB"."CUSTOMERS" ')
CHANGE FIELDS(#SELECTION) TO('WHERE STATE LIKE ''B!_%'' ESCAPE ''!''')
SELECT_SQL FIELDS(#CUSTNUM") USING(#TABLE + #SELECTION)
DISPLAY FIELDS(#CUSTNUM)
ENDSELECT
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[_]%'
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 three ways of converting the expression to Unicode to avoid the conversion error:
1. Pass the literal as a Unicode (UX'ssss') literal. For example, instead of:
'WHERE "UNIFLD" LIKE ''C%'''
try
'WHERE "UNIFLD" LIKE UX''00430025''').
2. Convert the literal or column using SQL functions so it becomes a Unicode expression. For example, CHARFLD is a Character column. Instead of
'WHERE "UNIFLD" = "CHARFLD"')
try
'WHERE "UNIFLD" = CAST "CHARFLD" AS GRAPHIC(6) CCSID 13488)')
For further details, refer to the IBM manual
Refer to Specifying File Names.
This is an optional parameter, unlike the parameter of the same name in the form of the SELECT_SQL command which is parsed by LANSA. This is because the file name used here does not effect the file accessed at runtime. It has two purposes: the first is for use in accessing Other Files that are in other databases. By specifying the file name LANSA will be able to locate 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.
The second purpose is to generate cross reference information so that use of the files in the USING parameter can be traced. This can later be used for impact analysis though clearly it is a manual cross reference and so relies on the programmer to keep it up to date. LANSA suggests that it should be made a program documentation rule.
Examples
. . . FROM_FILES(ORDLIN)
. . . USING('SELECT * FROM "MYLIB"."ORDLIN", "MYLIB"."ORDDTL"
. . . . . . WHERE "MYLIB"."ORDLIN"."CUSTNO" = "MYLIB"."ORDDTL"."CUSTNO"')
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.