Specifies whether or not SQL applies the ANSI/ISO SQL standard
for updatable views to views created during a session.
1 – 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
(B)0[m[1;4mSET[m[1m [1;4mVIEW[m[1m [1;4mUPDATE[m[1m [1;4mRULES[m[1m qq> runtime-options qqqq> [m [1m [m (B)0[m[1mruntime-options [m [1m [m [1mqqwqqq> 'string-literal' qqqqqqwqqqqq> [m [1m tqqq> parameter qqqqqqqqqqqqqu [m [1m mqqq> parameter-marker qqqqqqj [m
3 – Arguments
3.1 – 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.2 – 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.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.4 – 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.5 – '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
4 – 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