Thanks for your message, Nelson. The DataType can be obtained from any Field using Field.getDataType().
I hope this helps, Lukas On Tue, Jul 16, 2019 at 3:47 PM <nelson.dion...@gmail.com> wrote: > Thanks for the SQL query example, I'm facing the same issue and tries to > solve it on my hand, by generating with jOOQ the kind of SQL query you > posted. > > You mentioned: > >> Since jOOQ knows the data type of account.name, we can generate a >> non-null dummy value. > > > Did you have an idea on how to generate this value from jOOQ? I looked at > the DataType class, but did not find a way to do this. Any pointer would > help! > > Thanks! > > > On Thursday, May 2, 2019 at 4:53:38 PM UTC+2, Lukas Eder wrote: >> >> Well, the generated predicates would have to handle nulls first / nulls >> last, just like if those clauses aren't supported by a given RDBMS, e.g. >> when writing: >> >> ctx.selectFrom(account) >> .orderBy(account.name.nullsLast(), account.id) >> .seekAfter(nameValue, idValue) >> .fetch(); >> >> >> The generated SQL would have to be: >> >> SELECT * >> FROM account >> WHERE >> (CASE WHEN account.name IS NULL THEN 1 ELSE 0 END, COALESCE ( >> account.name, 'dummy'), account.id) >> >> > (CASE WHEN :nameValue IS NULL THEN 1 ELSE 0 END, COALESCE >> (:nameValue, 'dummy'), account.id) >> >> ORDER BY account.name NULLS LAST, account.id >> >> >> Since jOOQ knows the data type of account.name, we can generate a >> non-null dummy value. >> >> Good luck to your performance on that one :-) >> >> On Thu, May 2, 2019 at 4:43 PM <ma...@streetcontxt.com> wrote: >> >>> Hi Lukas, >>> >>> Yeah, it's a little bit messy to deal with, especially when trying to >>> generalize the coalesce statement to fields of other datatypes, but it >>> appears to be the only workaround for the moment. Not sure what can be done >>> for #2786, but looking forward to it! >>> >>> Thanks again for your help! >>> >>> Mani >>> >>> This e-mail message is intended for the named recipient(s) above, and >>> may contain information that is privileged, confidential and/or exempt from >>> disclosure under applicable law. If you have received this message in >>> error, or are not the named recipient(s), please do not read the content. >>> Instead, immediately notify the sender and delete this e-mail message. Any >>> unauthorized use, disclosure or distribution is strictly prohibited. >>> Quantify Labs Inc and the sender assume no responsibility for any errors, >>> omissions, or readers' misinterpretations and/ or understanding of the >>> content or transmission of this email. >>> >>> -- >>> 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 jooq...@googlegroups.com. >>> 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 jooq-user+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/ee24b65b-e91b-441e-827f-2253bcb48217%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/ee24b65b-e91b-441e-827f-2253bcb48217%40googlegroups.com?utm_medium=email&utm_source=footer> > . > 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 jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4fe9yXa0tb53Ffiko%3D1gFPkaZUivPomnBB-SJtVB8RZQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
