SQL/MX 3.2.1 Programming Manual for C and COBOL (H06.26+, J06.15+)

Name Resolution, Similarity Checks, and Automatic
Recompilation
HP NonStop SQL/MX Release 3.2.1 Programming Manual for C and COBOL—663854-005
8-16
Similarity Check

In the following example, the correct table mapping for the view similarity check
must be CAT.SCH.TABLE_ONE to PRDCAT.PRDSCH_ONE.TAB1 and
CAT.SCH.TABLE_ONE to PRDCAT.PRDSCH_ONE.TAB1.
Compile-time view:
CREATE VIEW TEMP_VIEW ENABLE SIMILARITY CHECK AS
SELECT I, J, A, B
FROM CAT.SCH.TABLE_ONE T1, CAT.SCH.TABLE_TWO T2
WHERE T1.I = T2.A;
Run-time view 1:
CREATE VIEW PRDTEMP_VIEW ENABLE SIMILARITY CHECK AS
SELECT I, J, A, B
FROM PRDCAT.PRDSCH_ONE.TAB2 T1, PRDCAT.PRDSCH_ONE.TAB1 T2
WHERE T2.I = T1.A;
Run-time view 2:
CREATE VIEW PRDTEMP_VIEW ENABLE SIMILARITY CHECK AS
SELECT I, J, A, B
FROM PRDCAT.PRDSCH_ONE.TAB2 T2, PRDCAT.PRDSCH_ONE.TAB1 T1
WHERE T1.I = T2.A;
Run-time view 3:
CREATE VIEW PRDTEMP_VIEW ENABLE SIMILARITY CHECK AS
SELECT I, J, A, B
FROM PRDCAT.PRDSCH_ONE.TAB1 T1, PRDCAT.PRDSCH_ONE.TAB2 T2
WHERE T1.I = T2.A;
Run-time view 1 fails the view similarity check because the view text is different
compared to the compile-time view, which is caused by a change in the position of
the predicate.
Run-time view 2 fails the view similarity check because the positions of the
correlation names (aliases) of the tables in the run-time view are different
compared to the compile-time view, which causes a difference in the view text.
In addition, run-time view 1 and view 2 definitions also result in wrong SQL/MX
tables being compared for the view similarity check.
Although the run-time views, view 1 and view 2 are semantically equivalent to the
compile-time view, they fail the view similarity check because of the difference in
the view text.
In run-time view 3, although the table names do not match, the view text matches
with the compile-time. Run-time view 3 also results in correct mapping of
underlying tables for the view similarity check. Therefore, the view similarity check
is passed.