CODEWITHSUNDEEP
javamysqljooq
user1573690
user1573690

Reputation: 361

Jooq query issue with case

I wrote this mysql query and tried to convert it to JOOQ query but not succeeded , this is mysql query 

SELECT `P`.`phone_number`, `A`.`emp_no`,   
SUM(CASE WHEN VCG.vid IN (
SELECT gv.vid FROM `grvas` gv JOIN gprs gr ON gr.id=gv.grid 
WHERE gr.id=G.id AND gv.stdate < '2011-08-15'  AND gv.enddate > '2011-09-14') 
THEN VCG.amount END) AS allow,
... etc...

How can i convert this query to JOOQ query ?
Thanks,

Upvotes: 2

Views: 1072

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220877

This:

SUM(
  CASE 
  WHEN VCG.vid IN (
    SELECT gv.vid 
    FROM `grvas` gv 
      JOIN gprs gr ON gr.id=gv.grid 
    WHERE gr.id=G.id 
      AND gv.stdate < '2011-08-15'  
      AND gv.enddate > '2011-09-14'
  ) 
  THEN VCG.amount 
  END
) AS allow

Would translate to something like this:

import static org.jooq.impl.DSL.*;

// And then
sum(
  decode()
  .when(VCG.VID.in(
      select(GRVAS.VID)
      .from(GRVAS)
        .join(GPRS).on(GPRS.ID.eq(GRVAS.GRID))
      .where(GPRS.ID.eq(G.ID))
      .and(GRVAS.STDATE.lt(Date.valueOf("2011-08-15")))
      .and(GRVAS.ENDDATE.gt(Date.valueOf("2011-09-14")))
  )), VCG.AMOUNT)
).as("allow")

The above example omitted table aliasing in the subquery of the IN predicate, to simplify things. Of course, you could go on and alias all those tables as well.

Upvotes: 4

user1573690
user1573690

Reputation: 361

I couldn't find a proper way to convert above sql to JOOQ but i used 

String sql="SELECT `P`.`phone_number`, `A`.`emp_no`,   
             SUM(CASE WHEN VCG.vid IN (
             SELECT gv.vid FROM `grvas` gv JOIN gprs gr ON gr.id=gv.grid 
             WHERE gr.id=G.id AND gv.stdate < '2011-08-15'  AND gv.enddate > '2011-09-14') 
             THEN VCG.amount END) AS allow,
             ... etc... " ;
Result<Record> res = f.fetch(sql);

This work fine Thanks

Upvotes: 0

Related Questions