JPAQueryFactory NVL function

Question

I am using this JPAQueryFactory .select to get the data

queryFactory.select(
            Projections.bean(detailDTO.class,
                    qEmpcate.id,
                    qEmcate.date.as("completeDate"),
                    qEmpcate.status,                       
                    qCete_name
                   ))
            .from(qEmpcate)
            .leftJoin(qCete).on(qCete.id.eq(qEmpcate.cete_id))
            .fetch();

complete date is sometimes NULL and it causes error in other components. In SQL I would fix it simply by using something like NVL(qEmcate.date,'') but I am quite new to JPAQueryFactory (Spring boot in general) and I realized it wouldnt be that easy. JPAQueryFactory doesnt recognize NVL and COALESCE is not working for me. Is there any other function in JPAQueryFactory that replaces NULL values with some String ?

Thank you for help.

In Querydsl you should use `coalesce` for this. What issue do you have with coalesce? — Jan-Willem Gmelig Meyling, Oct 26 '21 at 09:40
Because I want the NULL value of complete date to be empty string. Coalesce needs representation of both column and value to be same type - so in my case I will receive SQL error 22007 that representation of datetime and string was incorrect. To fix this I would need to use trim - COALESCE(trim(date), '') and I dont think I can use trim with coalesce that way in queryDLS — Peter Potoma, Oct 26 '21 at 12:14

JPAQueryFactory NVL function

0 Answers0