CHANGE QUERY Clause

Description

You can change the query defined in the query specification by using the CHANGE QUERY clause reserved word of the ALTER VIEW statement.

Syntax

ALTERVIEWVCLASS ] view_name

    CHANGE QUERY [ integer ] select_statement [ ; ]

Example

--adding select_statement which query number is 2 and 3 for each

ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id IN (1,2);

ALTER VIEW b_view ADD QUERY SELECT * FROM a_tbl WHERE id = 3;

SELECT * FROM b_view;

 

           id  phone

===================================

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

            4  NULL

            5  NULL

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

 

--altering view changing query number 2

ALTER VIEW b_view CHANGE QUERY 2 SELECT * FROM a_tbl WHERE phone IS NULL;

SELECT * FROM b_view;

 

           id  phone

===================================

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

            4  NULL

            5  NULL

            4  NULL

            5  NULL

            3  '333-3333'