Just create the view and omit variable portions. DB2 will compile the view into the overall query and, thus, apply any filtering specified inside the view in the same ways as filtering applied on top of the view:
Code:
CREATE VIEW db.view1 AS
SELECT n1.name, n2.address, n2.phonenum
FROM table1 AS n1, table2 AS n2
WHERE n1.name = n2.name
SELECT * FROM db.view1 WHERE phonenum = 1234567890
Alternatively, you could use table functions:
Code:
CREATE FUNCTION tf(phoneno VARCHAR(10))
RETURNS TABLE ( name VARCHAR(10), address VARCHAR(10), phoneno VARCHAR(10) )
RETURN
SELECT n1.name, n2.address, n2.phonenum
FROM table1 AS n1, table2 AS n2
WHERE n1.name = n2.name AND
n2.phonenum = phoneno
SELECT *
FROM TABLE ( tf('1234567890') ) AS t
dBforums
>
Creating View
06-29-07, 09:31
Thanks
Linear Mode
