Hello Manikandan, Thanks for reaching out on the user group as well. For completeness's sake, I'll cross-post my answer that I've given on Stack Overflow here, too.
Cheers, Lukas jOOQ supports MySQL's GROUP_CONCAT() function natively as DSL.groupConcat() <http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#groupConcat-org.jooq.Field->. Here's your corrected query with comments: // Don't forget, of course:import org.jooq.impl.DSL; And then // Use DSL.groupConcat() here dslContext.select(DSL.groupConcat(AcUserField.AcLineId)) .from(AcHeader) // Lower-case join() .join(AcDetails) .on(AcDetails.AcHeaderId.equal(AcHeader.AcHeaderId)) // Lower-case join() .join(AcUserField) .on(AcUserField.AcLineId.equal(AcDetails.AcLineId)) // Forgot to pass the parameter "1" .and(AcUserField.FieldNo.equal(1)) // Lower-case where() and use .equal() or .eq() rather than a Java assignment "=" .where(AcHeader.Company.equal(1)) .and(AcHeader.AccountNo.equal(190000)) // List values in in(...) predicate individually, don't put them all // in a single string. .and(AcHeader.Status.in("C", "D")).fetch() Optionally, if you prefer to use aliased versions of your tables, you can do so: AcHeader AH = AcHeader.as("AH");AcDetails AD = AcDetails.as("AD");AcUserField AUF = AcUserField.as("AUF"); dslContext.select(DSL.groupConcat(AUF.AcLineId)) .from(AH) .join(AD) .on(AD.AcHeaderId.equal(AcHeader.AcHeaderId)) .join(AUF) .on(AUF.AcLineId.equal(AD.AcLineId)) .and(AUF.FieldNo.equal(1)) .where(AH.Company.equal(1)) .and(AH.AccountNo.equal(190000)) .and(AH.Status.in("C", "D")).fetch() 2015-08-21 13:51 GMT+02:00 <[email protected]>: > Hi, > > Am working new in jooq. will you explain how do i use group_concat. Here i > have to group_concat one of the selection field. > > Mysql query: > SELECT group_concat(AUF.AcLineId )FROM AcHeader as AH INNER JOIN AcDetails > AS AD ON AD.AcHeaderId = AH.AcHeaderId INNER JOIN AcUserField AS AUF ON > AUF.AcLineId = AD.AcLineId AND AUF.FieldNo = 1 WHERE AH.Company = 1 AND > AH.AccountNo = 190000 AND AH.Status IN ('C', 'D') > > Jooq Query: > > dslContext.select(AUF.AcLineId).from(AcHeader).Join(AcDetails).on(AcDetails.AcHeaderId.equal(AcHeader.AcHeaderId)).Join(AcUserField > ).on(AcUserField.AcLineId.equal(AcDetails.AcLineId)) > .and(AcUserField.FieldNo.equal()).Where(AcHeader.Company=1) > .and(AcHeader=AccountNo=190000).and(Status.in("'C','D'")).fetch() > > > > http://stackoverflow.com/questions/32138417/how-do-i-write-jooq-select-query-for-the-following-mssql-query > > Kindly share your idea.. > > Regard, > Manikandan > > > On Sunday, March 18, 2012 at 5:11:44 PM UTC+5:30, TheDude wrote: >> >> Hello, >> >> I've started playing with jOOQ for a week now, using it as a sql >> querybuilder to use with spring's jdbcTemplate, and until now the >> framework is fullfilling my needs ! >> >> I'm now facing the following concern, which I don't know how to >> achieve, even since I've read the doc and browsed the api javadoc : >> >> Let's say I have a countries table : >> ccode char(2) PK, cname varchar(255) >> >> I'd like to produce the following resultset : >> 'fr', 'fr - France' >> >> The generated select statement (mysql syntax) should be like this : >> <pre> >> select `t`.`ccode`, concat(cast(`t`.`ccode` as char), ' - ', >> cast(`t`.`cname` as char)) as `value_` from `countries` as `t` order >> by `t`.`country` asc >> </pre> >> >> I would like to kow how to programmatically achieve it using the >> Factory.concat method, or maybe do have I >> missed something ? >> >> Thanks in advance and greetings from France! >> >> TL. >> > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
