SQL Query To JPQL

Question

SELECT * 
FROM   (SELECT van_num                vn, 
               berth_from             berth, 
               first_line_ashore_time first 
        FROM   iportman.ipt_pilotagerecord pilot 
        WHERE  ( Month(first_line_ashore_time) = 6 
                 AND Year(first_line_ashore_time) = 2013 ) 
               AND operation_movement = 'BERTHING') tab1 
       LEFT JOIN (SELECT van_num            vn, 
                         berth_from         berth, 
                         last_line_cast_off last 
                  FROM   iportman.ipt_pilotagerecord pilot 
                  WHERE  ( Month(last_line_cast_off) = 6 
                           AND Year(last_line_cast_off) = 2013 ) 
                         AND operation_movement = 'UNBERTHING') tab2 
              ON tab1.vn = tab2.vn

I want to execute the above query in JPQL. We are using JPA 2.0(Eclipselink 2.4.2 ). Please help me convert the SQL query to JPQL.

Thanks In advance

A subquery is allowed only in the SELECT and WHERE clauses in JPQL. Therefore, it is unlikely to translate this SQL directly into JPQL. — Lion
– Lion, Commented Jun 19, 2013 at 6:03
I think, you might want to create a view in whatever database system you're using which can fetch the desired result set (i.e you may write this SQL directly in that view) and then query this view through JPQL. — Lion
– Lion, Commented Jun 19, 2013 at 6:20

James · Accepted Answer · 2013-06-19 12:41:59Z

2

Note that JPA allows you to execute native SQL queries. For something this complicated, that is probably best.

Otherwise, you need to think in terms of objects, not data, what is your object model, and what object do you want back? Start by writing the query in English, and go from there.

The JPA spec does not allow sub-selects in the from clause, although EclipseLink does have some support for this. Not sure you query really needs to be doing this though, it seems much more complicated than it needs to be.

answered Jun 19, 2013 at 12:41

James

18.5k13 gold badges100 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query To JPQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related