HQL insert query with select, (unexpected token: order near line 11))

Question

I am trying to write a query for insert with select. but i am getting this error Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: order near line 11, column 23

Query query = hibSession.createQuery("""
              INSERT INTO
                  ProcessOrder(
                  version,
                  numberOfPeriods,
                  billingPeriodStart,
                  billingPeriodEnd,
                  invoice,
                  billingRun,
                  org,
                  order)
              SELECT
                  1L AS version,
                  1 AS numberOfPeriods,
                  ilt.billingStartDate AS billingPeriodStart,
                  ilt.billingEndDate AS billingPeriodEnd,
                  i AS invoice,
                  i.billingRun as billingRun,
                  i.org as org,
                  soi as order
              from
                  Invoice as i
                  join i.ordersIncluded as soi
                  join i.invoiceLineItems as ilt
              where
                  i.billingRun = 36 AND
                  i.status IN ('Outstanding','Paid')  AND
                  ilt.position = 1  """)

Please help to overcome this problem

K.C. K.C. · Accepted Answer · 2013-11-22T08:05:06

order is a reserved sql word. Better not to use it as an alias.

On that line, use for example : soi as soiOrder and of course also change in the insert order by soiOrder

You could also use:

.setResultTransformer(
    AliasToMapTransformer.renameAlias("soiOrder", "order").build()
)

With this class (copyright : original answer):

public class AliasToMapTransformer extends BasicTransformerAdapter {

  private Map<String, String> renameAliasMap;


  public AliasToMapTransformer(Map<String, String> renameAliasMap) {
    this.renameAliasMap = (renameAliasMap == null) ? Collections.<String, String>emptyMap() : renameAliasMap;
  }


  @Override
  public Object transformTuple(Object[] tuple, String[] aliases) {
    Map<String, Object> result = new HashMap<String, Object>(tuple.length);
    for (int i = 0; i < tuple.length; i++) {
        String alias = aliases[i];
        if (alias != null) {
            String newAlias = renameAliasMap.get(alias);

            result.put((newAlias != null) ? newAlias : alias, tuple[i]);
        }
    }
    return result;
  }


  public static Builder renameAlias(String alias, String newAlias) {
    return new Builder().renameAlias(alias, newAlias);
  }


  public static class Builder {

    private Map<String, String> aliasConversionMap = new HashMap<String, String>();


    public Builder renameAlias(String alias, String newAlias) {
        aliasConversionMap.put(alias, newAlias);
        return this;
    }


    public AliasToMapTransformer build() {
        return new AliasToMapTransformer(aliasConversionMap);
    }
  }
}

HQL insert query with select, (unexpected token: order near line 11))

1 Answers