Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2013
    Posts
    2

    Unanswered: Insert statement fails when using alias

    If I run a insert statement as

    insert into table m (m.x1,m.x2) values ('ss','yy')

    it failed in db2 saying that could not expect m. DB2 does not understand alias when use in insert.

    The above syntax works well in Oracle and I really don't know how to fix this issue as we are migrating from Oracle to DB2 and all the codes are written like that

    Any suggestion would really help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    At least, it might be Oracle dialects, in two points,
    (1) insert into table m ...
    (2) (m.x1,m.x2)

    I checked on Mimer SQL-2003 Validator
    Mimer SQL Developers - Mimer SQL-2003 Validator

    Example 1: Same as your insert statement.
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    insert into table m (m.x1,m.x2) values ('ss','yy')

    See Validator Examples

    Result:
    Code:
    insert into table m (m.x1,m.x2) values ('ss','yy') 
                ^----     ^    ^
    syntax error: table m
      correction: m
    syntax error: .
      correction: ,
    syntax error: .
      correction: ,
    Example 2: Remove table keyword
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    insert into m (m.x1,m.x2) values ('ss','yy')
    See Validator Examples

    Result:
    Code:
    insert into m (m.x1,m.x2) values ('ss','yy') 
                    ^    ^
    syntax error: .
      correction: ,
    syntax error: .
      correction: ,
    Example 3: Remove table keyword and qualifiers in column list.
    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    insert into m (x1,x2) values ('ss','yy')

    See Validator Examples

    Result:
    Code:
    Conforms to Core SQL-2003

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    your database is not in Oracle compat mode? There was another thread on the server this morning and the person was pointed to the info for converting an oracle app to DB2.

  4. #4
    Join Date
    Dec 2013
    Posts
    2
    db is on Oracle compatible mode.

    DB2_DEFERRED_PREPARE_SEMANTICS=YES
    DB2_COMPATIBILITY_VECTOR=ORA

Posting Permissions

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