Hello, I can reproduce your issue and here's the explanation:
caseWhen.getType() == Integer.class This type is inferred from your passing "0" to the when() method. Because you're using raw types, jOOQ allows you to call otherwise(Field<Object>) and lessOrEqual(String) when you should really use otherwise(Field<Integer>) and lessOrEqual(Field<String>) Now, the reason why you get "null" is because the lessOrEqual(T) method converts the right-hand-side to be of the same type as the left-hand-side, which doesn't work. Currently, the logic implemented in org.jooq.tools.Convert doesn't follow a "fail-fast" strategy, so you get "null" instead of some DataTypeException. The current strategy has been criticised before. We might change that in jOOQ 4.0... I've now registered an issue as a reminder: https://github.com/jOOQ/jOOQ/issues/3377 Now, for workarounds: 1. We generally suggest not to bypass generic typesafety. It is there to help you prevent such issues. You really shouldn't mix data types in your THEN / ELSE clauses of a CASE expression, not even in SQL. 2. When using the code generator, this kind of issue usually doesn't appear. Of course, that might not be an option for you. 3. Even if typesafety is applied correctly, you'll run into this issue because your caseWhen reference has a java.lang.Integer type. If you really don't want that type, you'll have to coerce types to java.lang.Object. See also: http://www.jooq.org/doc/latest/manual/sql-building/column-expressions/datatype-coercions/ I.e. possible workarounds are: Field caseWhen = DSL.coerce(DSL.decode().when(column.isNull(), 0).otherwise(column), Object.class); Field caseWhen = DSL.decode().when(column.isNull(), DSL.coerce(DSL.val(0), Object.class)).otherwise(column); Hope this helps, Lukas 2014-07-02 1:32 GMT+02:00 Jesse Plumb <[email protected]>: > Jooq version: 3.4.0 > > The following code: > > Field column = DSL.fieldByName("Table","Column"); > Field caseWhen = DSL.decode().when(column.isNull(), 0).otherwise(column); > Condition condition = caseWhen.lessOrEqual("Value"); > > System.out.println("Condition: " + condition); > > > outputs: > > Condition: case when "Table"."Column" is null then 0 else "Column" end <= > null > > expected: > > Condition: case when "Table"."Column" is null then 0 else "Table"."Column" > end <= "Value" > > > If I wrap the value as a field it doesn't make the field null, but I would > like to be able to compare the results of the case statement with a string > literal. Any suggestions for a workaround? > > -- > 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.
