CODEWITHSUNDEEP
sqljpa
Shiva kumar
Shiva kumar

Reputation: 13

SQL Query To JPQL

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

Upvotes: 0

Views: 98

Answers (1)

James
James

Reputation: 18389

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.

Upvotes: 2

Related Questions