JPA eclipselink oracle db error: 不是选定的表达式

Question

Order                 OrderItem                                     Product
-----------------------------------------------------------------------------
id                    id,orderId,proudctId,unitPrice,quantity       id, barcode

JPA eclipseLink：按订单 ID、产品条形码和数量列出订单项：

select * from OrderItem order by orderId asc, 
lpad(product.barcode, 20, '0') desc, quantity asc
Query: ReadAllQuery(referenceClass=OrderItem sql="SELECT * FROM (SELECT a.*,
ROWNUM rnum  FROM (SELECT DISTINCT t1.ID AS a1, t1.QUANTITY AS a2, 
t1.UNITPRICE AS a3, t1.orderId AS a4, t1.productId AS a5, lpad(t0.barcode, ?, ?)
FROM Product t0, OrderItem t1 WHERE (t0.ID = t1.productId) ORDER BY t1.orderId 
ASC, lpad(t0.barcode, ?, ?) DESC, t1.QUANTITY ASC) a WHERE ROWNUM <= ?) WHERE 
rnum > ?")

错误 ORA-01791：不是选定的表达式

如果在oracle sqlPlus中执行SQL，则没有错误。如果只是按 product.barcode 而不是 lpad(...( 订购，它可以工作。

Answer 1

假设你想按相同的表达式lpad(p.barcode, ?, ?)排序，改用它的别名。试试这个：

select *
from (
    select a.*,
        ROWNUM rnum
    from (
        select distinct o.ID as a1,
            o.QUANTITY as a2,
            o.UNITPRICE as a3,
            o.orderId as a4,
            o.productId as a5,
            lpad(p.barcode, ?, ?) as a6
        from Product p
        join OrderItem o on p.ID = o.productId
        order by a4,
            a6 desc,
            a2
        ) a
    where ROWNUM <= ?
    )
where rnum > ?

还要注意显式连接语法，它比旧的基于逗号的连接语法和表的重命名别名更现代、更清晰。