Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Insert into view that is using join fails

    Hi,
    using DB2 v9.5 I have the following sample:

    db2 create table admin.tab1 (col1 int not null primary key, col2 char(20), col3 int)
    db2 create table admin.tab2 (col3 int not null primary key, col4 char(20))
    db2 alter table admin.tab1 add foreign key (col3) references admin.tab2 (col3) on delete restrict on update restrict
    db2 create view admin.view as select a.col1, a.col2, b.col3, b.col4 from admin.tab1 a, admin.tab2 b where a.col3=b.col3
    db2 insert into admin.view values (1,'AAA',100, 'BBB')

    Insert statements fails with error message:
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0150N The target fullselect, view, typed table, materialized query table, or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a target for which the requested operation is not permitted. SQLSTATE=42807

    Why this insert does not work?
    Regards

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    It looks there is a limitation when inserting into view. I checked documentation:

    ==========
    A view or a fullselect is read only if the SELECT statement of
    the view or the fullselect contains any of the following:

    A FROM clause that identifies one of the following:

    - More than one table or view

    ==========
    Last edited by grofaty; 02-18-09 at 03:18.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can use INSTEAD OF Triggers to 'write' to views.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    sathyaram_s, thanks, that is good point.

Posting Permissions

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