1 SET_VIEW_UPDATE_RULES Specifies whether or not SQL applies the ANSI/ISO SQL standard for updatable views to views created during a session. 2 Environment You can use the SET VIEW UPDATE RULES statement: o In interactive SQL o Embedded in host language programs to be precompiled o As part of a procedure in an SQL module o In dynamic SQL as a statement to be dynamically executed 2 Format SET VIEW UPDATE RULES --> runtime-options ----> runtime-options --+---> 'string-literal' ------+-----> +---> parameter -------------+ +---> parameter-marker ------+ 2 Arguments 3 parameter Specifies the value of runtime-options, which must be one of the following: o SQL99 o SQL92 o SQL89 o MIA o SQLV40 3 parameter-marker Specifies the value of runtime-options, which must be one of the following: o SQL99 o SQL92 o SQL89 o MIA o SQLV40 3 SQL99_or_SQL92 Specifies that the ANSI/ISO standard for updatable views is applied to all views created during compilation. Views that do not comply with the ANSI standard for updatable views cannot be updated. The ANSI/ISO standard for updatable views requires the following conditions to be met in the SELECT statement: o The DISTINCT keyword is not specified. o Only column names can appear in the select list. Each column name can appear only once. Functions and expressions such as max(column_name) or column_name +1 cannot appear in the select list. o The FROM clause refers to only one table. This table must be either a base table or a derived table that can be updated. o The WHERE clause does not contain a subquery. o The GROUP BY clause is not specified. o The HAVING clause is not specified. 3 SQLV40 Specifies that the ANSI/ISO SQL standard for updatable views is not applied. SQL considers views that meet the following conditions to be updatable: o The DISTINCT keyword is not specified. o The FROM clause refers to only one table. This table must be either a base table or a derived table that can be updated. o The WHERE clause does not contain a subquery. o The GROUP BY clause is not specified. o The HAVING clause is not specified. The default is SQLV40. 3 'string-literal' Specifies the value of runtime-options, which must be one of the following: o SQL99 o SQL92 o SQL89 o MIA o SQLV40 2 Example Example 1: Setting the view characteristics from SQLV40 to SQL99 SQL> ATTACH 'ALIAS ENV1 FILENAME ENVIRONMENT'; SQL> CONNECT TO 'ALIAS ENV1 FILENAME ENVIRONMENT' AS 'TEST'; SQL> SHOW CONNECTIONS TEST Connection: TEST Default alias is RDB$DBHANDLE Default catalog name is RDB$CATALOG Default schema name is SMITH Dialect: SQLV40 Default character unit: OCTETS Keyword Rules: SQLV40 View Rules: SQLV40 Default DATE type: DATE VMS Quoting Rules: SQLV40 Optimization Level: DEFAULT Hold Cursors default: WITH HOLD PRESERVE NONE Quiet commit mode: OFF Compound transactions mode: EXTERNAL Default character set is DEC_MCS National character set is DEC_MCS Identifier character set is DEC_MCS Literal character set is DEC_MCS Display character set is UNSPECIFIED Alias ENV1: Identifier character set is DEC_MCS Default character set is DEC_MCS National character set is KANJI SQL> -- SQL> -- Change the environment for view rules from SQLV40 to SQL99 SQL> -- SQL> SET VIEW UPDATE RULES 'SQL99'; SQL> SHOW CONNECTIONS TEST Connection: TEST Default alias is RDB$DBHANDLE Default catalog name is RDB$CATALOG Default schema name is SMITH Dialect: SQLV40 Default character unit: OCTETS Keyword Rules: SQLV40 View Rules: ANSI/ISO Default DATE type: DATE VMS Quoting Rules: SQLV40 Optimization Level: DEFAULT Hold Cursors default: WITH HOLD PRESERVE NONE Quiet commit mode: OFF Compound transactions mode: EXTERNAL Default character set is DEC_MCS National character set is DEC_MCS Identifier character set is DEC_MCS Literal character set is DEC_MCS Display character set is UNSPECIFIED Alias ENV1: Identifier character set is DEC_MCS Default character set is DEC_MCS National character set is KANJI