Hello,
here are the JPQL and the SQL issued to de database (I´m sorry, the names of the tables and the fields are not in English):
The JPQL query:
SELECT a FROM AlarmaH a LEFT JOIN a.usuarioAAct u ORDER BY a.usuarioAAct.nombre
it is translated to SQL:
SELECT t0.ID, t0.DIS_GRUPO, t0.ESTADO, t0.OBSERVACIONES, t0.FECHAACT, t0.FECHAOBS, t0.FECHAAACT, t0.SEVERIDAD, t0.CAMPON2, t0.DIRECCIONIPEQUIPO, t0.CAMPON4, t0.DESCRIPCION, t0.CAMPON3, t0.CAMPOT1, t0.FECHABOR, t0.FECHARACT, t0.IDMODULO, t0.USUARIOAACT, t0.USUARIOBOR, t0.IDESTACION, t0.IDCODIGOALARMA, t1.ID, t1.FECHAAREP, t1.FECHAREP, t1.FECHARREP, t1.ENTRADA, t1.USUARIOAREP, t2.ID, t3.ID, t3.FECHAREP, t3.ZONA, t3.USUARIOAREP, t4.ID, t4.FECHARREP, t4.FECHAAREP, t4.FECHAREP, t4.PERMITIDO, t4.PERSONAL, t4.USUARIOAREP
FROM ALARMASH t0
LEFT OUTER JOIN ALARMASTECNICAH t1 ON (t1.ID = t0.ID)
LEFT OUTER JOIN ALARMASNOTIFICACIONH t2 ON (t2.ID = t0.ID)
LEFT OUTER JOIN ALARMASSEGURIDADH t3 ON (t3.ID = t0.ID)
LEFT OUTER JOIN ALARMASACCESOH t4 ON (t4.ID = t0.ID),
USUARIOS t5
WHERE (t5.ID = t0.USUARIOAACT)
ORDER BY t5.NOMBRE DESC
(I´m also sorry for the big chunk).
So, as you can see, Toplink is generating an INNER JOIN (called USUARIOS t5) instead of a LEFT OUTER JOIN, and that´s why it filters out the columns with NULL values. That is, I think it ignores the join for the user if it has to make a join for the ORDERBY clause. Is that correct?
So the question now is, how could I obtain those column with NULL values if I include an ORDERBY clause?
Maybe I´ll have to do a UNION select, one part with the ORDERBY clause and the other without it but forcing to obtain the NULL values with an IS NULL clause, or something like that?
Anyway, thank you very much for the answers.
Regards,
David.
[Message sent by forum member 'dvacasp' (dvacasp)]
http://forums.java.net/jive/thread.jspa?messageID=269478