HQL and concat expression

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

HQL and concat expression

Post by shuddleston » Fri Feb 10, 2012 5:01 pm

Hi Support,

I'm getting a GenericJDBCError when issuing the following query

from JobOrder job
where concat(job.publicDescription,' ',job.title) like '%test%'

According to the Hibernate docs for HQL the concat function is supported. I referenced the 3.3. docs, not sure what version you are on, but its been supported for some time I believe.

I understand I can rewrite the above query, but i wanted to check on support for concat.

Thanks,

Scott

r.knight
User
Posts: 68
Joined: Wed Dec 31, 1969 8:00 pm

Re: HQL and concat expression

Post by r.knight » Mon Feb 13, 2012 2:14 pm

Dear shuddleston,

Unfortunately I am not too familiar with the concat expression and can't provide thourough advice about this. How is this being used in relation to the API's? Do you have a local database on your server that is being queried?

If you have any issues with the SOAP requests or responses I should be able to help further.

Thanks,

Ryan Knight | Senior Enterprise Analyst
BULLHORN® | Follow Us
Ryan Knight
Principal Enterprise Support Analyst and Team Lead
B U L L H O R N
33-41 Farnsworth Street, 5th Floor, Boston, MA 02210
US Support: 617-478-9126
International Support: +1 617 478 9131
http://www.bullhorn.com

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

Re: HQL and concat expression

Post by shuddleston » Mon Feb 13, 2012 3:30 pm

I'm using the query operation to find JobOrder entities with version 1.1 of the web services API. I am not having any SOAP request or response issues.

I'd like to keyword search the following properties of the JobOrder as a single chunk of text: publicJobDescription and address.zip. For example, if a user enters a search string "94110 software engineer" I would like to return jobs that have all of the keywords found the the abouve chunk of text. This could be expressed in HQL as

from JobOrder job
where contcat(job.publicJobDescription,' ',job.address.zip) like '%94110%'
and contcat(job.publicJobDescription,' ',job.address.zip) like '%software%'
and contcat(job.publicJobDescription,' ',job.address.zip) like '%engineer%'

where concat() concatenates the columns together to form my chunk of text to search. Since you guys use SQL Server Hibernate would translate this into arg1 + arg2 + ... or perhaps use concat() directly if you're on SQL Server 2012. I was attempting to utlize the database rather than tokenizing the keywords and identifying which may be postal codes by just searching a chunk of text. I understand this may not be the most efficient of queries.

If I were to tokenize the keywords and detect postal codes I could split the keywords up and constrain by the appropriate properties. This could be expressed in HQL as

from JobOrder job
where publicDescription like '%software engineering% and address.zip like ' %94110%'

So I just wanted to see if the concat function was supported in the where clause when using the query operation. It is a supported HQL function, however when I use it I receive a stacktrace with GenericJDBCException as the error.

Thanks,

Scott

r.knight
User
Posts: 68
Joined: Wed Dec 31, 1969 8:00 pm

Re: HQL and concat expression

Post by r.knight » Tue Feb 14, 2012 10:18 am

Dear shuddleston,

I will have to check with our devopment team regarding your question. I will get back to you soon.

Thanks,

Ryan Knight | Senior Enterprise Analyst
BULLHORN® | Follow Us
Ryan Knight
Principal Enterprise Support Analyst and Team Lead
B U L L H O R N
33-41 Farnsworth Street, 5th Floor, Boston, MA 02210
US Support: 617-478-9126
International Support: +1 617 478 9131
http://www.bullhorn.com

r.knight
User
Posts: 68
Joined: Wed Dec 31, 1969 8:00 pm

Re: HQL and concat expression

Post by r.knight » Thu Feb 16, 2012 12:15 pm

Dear shuddleston,

I spoke with a member of the engineering team regarding your question. Unfortunately we do not support the concat() function as an argument in the where clause, at this time.

Thanks for your patience and if there is anything else that I can help with, don't hesitate to ask.

Thanks,

Ryan Knight | Senior Enterprise Analyst
BULLHORN® | Follow Us
Ryan Knight
Principal Enterprise Support Analyst and Team Lead
B U L L H O R N
33-41 Farnsworth Street, 5th Floor, Boston, MA 02210
US Support: 617-478-9126
International Support: +1 617 478 9131
http://www.bullhorn.com

Post Reply