Coalesce function in Order By of query operation

Forum for users and developers of Bullhorn's Webservices API suite

Moderators: StaffingSupport, s.emmons, BullhornSupport

Post Reply
shuddleston
User
Posts: 27
Joined: Tue Feb 03, 2009 12:00 pm

Coalesce function in Order By of query operation

Post by shuddleston » Fri Feb 15, 2013 9:06 am

Hi,

I'm querying for JobOrder entities using the following HQL

from JobOrder job
where job.status in (:job_status00,:job_status01,:job_status02,:job_status03,:job_status04,:job_status05,:job_status06) AND
job.isPublic = :job_isPublic1 AND
job.isOpen = :job_isOpen2 AND
job.isDeleted = :job_isDeleted3
order by coalesce(job.customText19,job.title) ASC

where job.customText19 is a custom field on the Job entity we are using for an alternate title for the job. The query runs without issue, however the sort order is not what I expected. My goal with using the coalesce function was to use the customText19 field if non-null, otherwise use the title field which should always be non-null (it is a required field).

The records seem to be sorted by jobId in ascending order for those records whose customText19 field is null and then by customText19 in ascending order.

Can you confirm I can put coalesce in my HQL? The coalesce() function is supported by Hibernate 3.5 through 4.1 (and probably much older versions as well). I also tried a "case job.customText19 when null then job.title else job.customText19 end ASC" in place of the coalesce() and received the same ordering of results.

Thanks,

Scott

c.choy
User
Posts: 49
Joined: Wed Dec 31, 1969 8:00 pm

Re: Coalesce function in Order By of query operation

Post by c.choy » Mon Feb 18, 2013 11:04 am

Hi,

Looks like you can run it, but don't forget that when you evaulate the expression, it's returning the value of the expression. So you're not so much returning 'customtext9' or 'title', but rather whatever string you have in customtext9 or title, such as 'random text' or 'programmer'

If you can get the case to work properly (I can't using the tools I have), then it's simple enough to test the value and then return 'customtext9' or 'title' and I believe that should work.

shuddleston
User
Posts: 27
Joined: Tue Feb 03, 2009 12:00 pm

Re: Coalesce function in Order By of query operation

Post by shuddleston » Fri Feb 22, 2013 12:31 pm

Thanks for the feedback.

I did intend to use the value the coalesce function returns and not the property name of the entity that is mapped. Hibernate should translate the HQL into a SQL statement and I would expect the coalesce function to be translated into the equivalent SQL function for the target database. In this case, I believe the target database is MS SQL Server which does support coalesce.

The query results seem to be sorted by customText19 and then by title which is not what I expected. I've tried a similar SQL query using the coalesce function (and also the equivalent case statement form) in the order by on a local database of jobs and it works as expected.

Since I did not receive an error I just wanted to confirm it is valid to use coalesce in the order by in Bullhorn's support of HQL. Seems like it might get stripped out by some preprocessing or I'm just using it wrong.

Thanks,

Scott

shuddleston
User
Posts: 27
Joined: Tue Feb 03, 2009 12:00 pm

Re: Coalesce function in Order By of query operation

Post by shuddleston » Mon Feb 25, 2013 2:35 pm

Is anyone from Bullhorn able to comment on this?

Thanks,

Scott

shuddleston
User
Posts: 27
Joined: Tue Feb 03, 2009 12:00 pm

Re: Coalesce function in Order By of query operation

Post by shuddleston » Wed Feb 27, 2013 9:10 am

Andrew, Sam or Ryan any thoughts on this?

Thanks,

Scott

s.emmons
User
Posts: 333
Joined: Wed Dec 31, 1969 8:00 pm

Re: Coalesce function in Order By of query operation

Post by s.emmons » Mon Mar 18, 2013 4:37 pm

Hi Scott,

Yes, that should work.
Sam Emmons
Enterprise Team Lead
BULLHORN
US Support 617-478-9126

Post Reply