[SOLVED] jooq iif or nvl2 in request

jooq iif or nvl2 in request

I can’t figure out what I’m specifying incorrectly, null values from the STUDENT.CURRENT_CURS_ID column are not processed by iif or nvl2 and in general this student with a null value is not included in the list of students

As soon as I set the real value STUDENT.CURRENT_CURS_ID instead of null, the student ends up in select

  var content =
     context
        .select(
            STUDENT.ID,
            STUDENT.NAME,
    //        nvl2(STUDENT.CURRENT_CURS_ID, currentCurs.CURS, ""),
              iif(STUDENT.CURRENT_CURS_ID.isNull(), "", currentCurs.CURS),
            futureCurs.CURS)
        .from(STUDENT)
        .innerJoin.......
        .fetchInto(StudentInfo.class);

I checked that it works on a column with data type string iif and nvl2, but STUDENT.CURRENT_CURS_ID has type uuid and for some reason does not work with iif and nvl2. I can’t find if there are any solutions to this?

Solution

You're probably inner joining on the CURRENT_CURS_ID. That means, if it's NULL, then the INNER JOIN will filter out the record that you meant to keep.

Just use a .leftJoin() instead