Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Unanswered: using alias for column names in an insert?

    Good day.
    Has anyone heard of using alias names in an insert statement ?

    We have someone on our project who says he needed shorter column names
    for a myBatis lookup bean (30 characters or less) and has set up an insert
    myBatis xml file with a resultMap and the insert above it (no select statement in this xml file) with aliases, something like:

    <insert ... ... >
    insert into <schema>.<table> (
    field1,
    field2 as fld2,
    field3 as fld3,
    field4 as fld4,
    field5,
    field6 )
    VALUES (
    #{ bean.field1},
    #{ bean.field2},
    #{ bean.field3},
    #{ bean.field4},
    #{ bean.field5},
    #{ bean.field6} )
    SELECT * FROM DUAL
    </insert>

    My question is: I've never seen aliases used in an insert, so are they
    legal, and will they work ? The database used is Oracle.
    The unit test for the DAO will not run with the aliases in place: it gives
    a SQL error - ORA-00907: missing right parenthesis.

    Has anyone seen this used successfully?
    Even if someone has, I don't think it's a good practice: it's just a
    band-aid for someone being lazy and not changing the column names
    to be shorter.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    There's nothing in the standards, to my knowledge, that will help you.

    However a definite kludge/workaround/sticking-plaster solution would be to create a view with the shorter column names and insert via this layer of abstraction:

    Code:
    CREATE VIEW schema.view_name
      AS
    SELECT field1 As f1
          , field2 As f2
          , field3 As f3
          , ...
          , fieldN As fN
    FROM   schema.table_name
    ;
    
    INSERT INTO schema.view_name (f1, f2, f3, ..., fN)
      VALUES ('a', 'b', 'c', '...', 'N')
    ;
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Are values being inserted for all columns? Or are some of them being left for the default? If inserting each columns value, then do the insert as:
    Code:
    INSERT INTO schema.view_name
      VALUES ('a', 'b', 'c', '...', 'N')
    ;
    Dave

Posting Permissions

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