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

    Unhappy Unanswered: Anonymous Table Name [ORA-00903]

    Hi,

    We used to use write simple statements in DB2 like below. But, after migrating to Oracle 11g, the script isn't working. Can anyone help the equivalent and without using UNION.

    DB2 Script:
    Code:
    SELECT * FROM (VALUES (1,1), (1,2), (2,1), (2,2)) as T (a, b);
    Oracle Output:
    Code:
    Error starting at line 1 in command:
    SELECT * FROM (VALUES (1,1), (1,2), (2,1), (2,2)) as T (a, b)
    Error at Command Line:1 Column:15
    Error report:
    SQL Error: ORA-00903: invalid table name
    00903. 00000 -  "invalid table name"
    *Cause:    
    *Action:
    Thanks in advance.


    Regards,
    Rohit

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Rohit Oberoi View Post
    We used to use write simple statements in DB2 like below. But, after migrating to Oracle 11g, the script isn't working.
    Correct, because Oracle does not support the row value constructor (outside of an INSERT statement

    Can anyone help the equivalent and without using UNION
    Not possible.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    You may use multi table insert:
    Code:
    insert all
      into T (a, b) VALUES (1,1)
      into T (a, b) VALUES (1,2)
      into T (a, b) VALUES (2,1)
      into T (a, b) VALUES (2,2)
    select dummy from dual;
    Not sure why you refuse UNION (the expression in the FROM clause seem to do it behind the scene), if there is any logic behind those values (seem to be all permutations with repetion of two numbers), there may be better ways to generate them (even with UNION).

Tags for this Thread

Posting Permissions

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