Results 1 to 3 of 3

Thread: Creating View

  1. #1
    Join Date
    Jul 2005
    Posts
    47

    Unanswered: Creating View

    Hi Everyone,

    Can anyone please share some ideas on how to create a view where you can input the variable conditions such as

    Create view DB.View1 as select N1.Name,N2.Address,N2.Phonenum from
    table1 N1,table2 N2 where N1.Name=N2.Name and N2.Phonenum = 1234567890 ;

    The Phone Number is variable.I would like to have the option of specifying the phone number as input to the View.
    234567891
    987654321.

    Please suggest a solution.Thanks in Advance.
    Venkat

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jul 2005
    Posts
    47

    Smile Thanks

    Thanks for the reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •