Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    20

    Unanswered: help find similar query

    Hello,
    how can i write similar query?
    Code:
    insert all 
    into vlaptop (screen) values(15)
    into vlaptop (model, speed,ram,hd,price)
    select model+1100,speed,ram,hd,price+500
    from pc;
    it returns an error:"ora-01702 a view is not appropriate here"
    vlaptop is indeed a view, how can i rewrite the query so it will insert those values into the view ?
    thank you

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,112
    Provided Answers: 5
    It appears that all columns mentioned in this INSERT statement (screen, model, speed, ram, hd, price) identify the same computer. Do you realize that such an INSERT statement inserts 2 records, not 1?
    Code:
    SQL> CREATE TABLE TEST
      2  (screen    NUMBER,
      3   ram       NUMBER,
      4     price     NUMBER
      5  );
    
    Table created.
    
    SQL> INSERT ALL
      2    INTO TEST (screen) VALUES (15)
      3             INTO TEST (ram, price) VALUES (1, 500)
      4  SELECT * FROM dual;
    
    2 rows created.
    
    SQL> SELECT * FROM TEST;
    
        SCREEN        RAM      PRICE
    ---------- ---------- ----------
            15
                        1        500
    
    SQL>
    I *suppose* all these values should be inserted as one record:
    Code:
    SQL> INSERT INTO TEST (screen, ram, price)
      2    (SELECT 15, 1, 500 FROM dual);
    
    1 row created.
    
    SQL> SELECT * FROM TEST;
    
        SCREEN        RAM      PRICE
    ---------- ---------- ----------
            15          1        500
    
    SQL>
    (never mind stupid SELECT ... FROM DUAL in this example).
    Therefore, I'd say that it is not a view itself that should bother you. Also, an ordinary INSERT can do the job:
    Code:
    SQL> CREATE VIEW v_test AS
      2    SELECT 15 screen, 1 ram, 500 price FROM dual
      3    UNION
      4    SELECT 21, 3, 1799 FROM dual;
    
    View created.
    
    SQL> INSERT INTO TEST (screen, ram, price)
      2    SELECT screen, ram, price FROM v_test;
    
    2 rows created.
    
    SQL> SELECT * FROM TEST;
    
        SCREEN        RAM      PRICE
    ---------- ---------- ----------
            15          1        500
            21          3       1799
    
    SQL>
    Though: if I misunderstood the problem, could you explain it once again, please?

  3. #3
    Join Date
    Nov 2008
    Posts
    20
    I got it now, thanks Littlefoot!

Posting Permissions

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