HQL case in ORDER BY not sorting correctly

Question

I have an HQL that I can choose to order by "studentNumber" or its name.

    String orderBy = orderByNumber
            ? "(case when e.studentNumber is null then e.student.name else e.studentNumber end)" : "e.student.name";

But when if I choose studentNumber, and it is null, I should order by name. For that I used:

   (case when e.studentNumber is null then e.student.name else e.studentNumber end)

This works fine, but when I have more than 10 students the order comes like this:

    1
    10
    11
    12
    2
    3
    4
    5
    6
    7
    8
    9

studentNumber is set as Integer in the database and entity. I guess this would happen if it was considered a string.

thank you

EDIT 1 I tried this new query:

    (case when (case when e.studentNumber is null then e.student.name else e.studentNumber end) = e.studentNumber then cast(e.studentNumber as int) end)

now it orders correctly, but when e.studentNumber is null, is not ordering by name. If I add the then and try this:

    (case when (case when e.studentNumber is null then e.student.name else e.studentNumber end) = e.studentNumber then cast(e.studentNumber as int)  else e.student.name end)

The first problem comes back from the dead as a zombi (1, 10, 2, 3)...

EDIT 2

I also tried with coalesce:

   (case when coalesce(e.studentNumber, e.student.name) = e.studentNumber then cast(e.studentNumber as int) else e.student.name end)

Same as above, in both cases.

Answer 1

I think it's a sql error. In the "Case" you are returning two different datatype, the sql is autocasting the number type to satisfy some kind of logic. I try to do something similar in an oracle database and it result in an error that said that the returning datatypes are different. You should try to change you request. Maybe you can try to cast the result in a number if the studentNumber is not null https://stackoverflow.com/a/2000061/3543153

Edit 1: What's about symply using something like

ORDER BY e.studentNumber NULLS LAST, e.student.name NULLS LAST