Page History
7.110.1 SELECT_SQL Free Format Parameters
|
|---|
Specifies the fields that will receive the result of the SQL command specified in the USING parameter.
...
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.
| Info |
|---|
| 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 |
...
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
| Info |
|---|
| 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, refer to the IBM manual DB2 UDB for IBM SQL Reference.
|
|---|
Refer to Specifying File Names.
...
. . . 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.
...
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.
...