Paged query on entity with calculated field

Question

I have a paged query on a view where one of the fields is anottated with @Formula (Using ajahe ebean query).

When running the query on a page > 0 I get an error:

PersistenceException: Query threw SQLException:No column name was specified for column 8 of 'limitresult'.

The generated query is:

select * 
from ( 
  select top 20 row_number() over (order by product_id) as rn, 
    t0.product_id c0, t0.isbn c1, 
    t0.provider_external_id c2, 
    t0.total_inventory c3, 
    t0.total_fulfilled c4, 
    t0.total_not_for_sale c5, 
    total_inventory - total_fulfilled - total_not_for_sale
  from product_stock_status t0 
  order by product_id 
) 
as limitresult where rn > 10 and rn <= 20 .

Running this query directly on the database (mssql) results in the same exception.

Adding alias to the generated value total_inventory - total_fulfilled - total_not_for_sale solves the problem, however I don't know how to get the framework to add an alias to get the query to work.

Thanks

score 0 · Accepted Answer · answered Dec 31 '15 at 13:11

The solution was to add an alias to the formula definition: instead of @Formula(select = "total_inventory - total_fulfilled - total_not_for_sale")

Changed to @Formula(select = "total_inventory - total_fulfilled - total_not_for_sale as totalAvailable")

Paged query on entity with calculated field

1 Answers1