Sort sequence and views

 

Views are created with the sort sequence that is in effect when the CREATE VIEW statement is run.

When the view is referred to in a FROM clause, that sort sequence is used for any character comparisons in the subselect of the CREATE VIEW. At that time, an intermediate result table is produced from the view subselect. The sort sequence in effect when the query is being run is then applied to all the character and UCS-2 graphic comparisons (including those comparisons involving implicit conversions to character, or UCS-2 or UTF-16 graphic) specified in the query. The following SQL statements and tables show how views and sort sequences work. View V1, used in the following examples, was created with a shared-weight sort sequence of SRTSEQ(*LANGIDSHR) and LANGID(ENU). The CREATE VIEW statement is as follows:

CREATE VIEW V1 AS SELECT *

  FROM STAFF   WHERE JOB = 'MGR' AND ID < 100

Table 1. "SELECT * FROM V1"

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
10	Sanders	20	Mgr	7	18357.50	0
30	Merenghi	38	MGR	5	17506.75	0
50	Hanes	15	Mgr	10	20659.80	0

Any queries run against view V1 are run against the result table shown above. The query shown below is run with a sort sequence of SRTSEQ(*LANGIDUNQ) and LANGID(ENU).

Table 2. "SELECT * FROM V1 WHERE JOB = 'MGR'" using the unique-weight sort sequence for ENU language identifier

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
30	Merenghi	38	MGR	5	17506.75	0

 

Parent topic:

Sort sequences and normalization in SQL