searching JobOrder query error

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

Moderators: StaffingSupport, s.emmons, BullhornSupport

Post Reply
mgeltser
User
Posts: 19
Joined: Wed Dec 31, 1969 8:00 pm

searching JobOrder query error

Post by mgeltser » Wed Aug 15, 2012 5:18 pm

The following where clause:
status in ('Lost-Potential Order', 'Lost-Order') and reasonClosed = 'N/A'
is failling while searching for JobOrder entity.

Can you please tell me what is wrong with it?

Thanks,
Mike

juliet
User
Posts: 6
Joined: Tue Jul 10, 2012 11:43 am

Re: searching JobOrder query error

Post by juliet » Wed Aug 22, 2012 1:18 pm

Hello Mike,

This is Julie from Bullhorn Support, I will be assisting you with your question. It would be helpful if you could provide us with the error message that you are seeing when running this query on the JobOrder entity. Please reply back with the error message that you are receiving.

Best Regards,
Julie Thomas
Enterprise Support Analyst
B U L L H O R N
Staffing and Recruiting Software, On Target, On Demand
33-41 Farnsworth Street, 5th Floor, Boston, MA 02210
617-478-9126 (main)
www.bullhorn.com

mgeltser
User
Posts: 19
Joined: Wed Dec 31, 1969 8:00 pm

Re: searching JobOrder query error

Post by mgeltser » Wed Aug 22, 2012 2:43 pm

The query i am trying now is this:
isDeleted = 0 and employmentType in ('HR', 'LT', 'SME') and status in ('Lost-Potential Order', 'Lost-Order') and Len(reasonClosed) < 4

Below is the error message
-99999: org.hibernate.exception.GenericJDBCException: could not execute query

juliet
User
Posts: 6
Joined: Tue Jul 10, 2012 11:43 am

Re: searching JobOrder query error

Post by juliet » Wed Aug 22, 2012 4:55 pm

Hello Mike,

Thanks for providing the requested information. You are receiving the error message due to the way the datatype for reasonClosed is stored in the Bullhorn database. 'reasonClosed' is stored as a text value in our database, which isn't compatible with comparison operators (such as the = sign). You can do two things in order to successfully execute your query: use the LIKE operator or convert the reasonClosed field to a varchar (which will treat the reasonClosed field as a varchar at runtime).

Here are some examples of the syntax for my above suggestions in XML:

<where>(status = 'Lost-Potential Order' OR status = 'Lost-Order') AND (employmentType = 'HR' OR employmentType = 'LT' OR employmentType = 'SME') AND isDeleted = 0 AND reasonClosed LIKE '%N/A%'</where>

<where>(status = 'Lost-Potential Order' OR status = 'Lost-Order') AND (employmentType = 'HR' OR employmentType = 'LT' OR employmentType = 'SME') AND isDeleted = 0 AND CONVERT(VARCHAR(20), reasonClosed) = 'N/A'</where>

I hope this helps! Please let me know if you have any questions.

Thanks,

Julie Thomas
Enterprise Support Analyst
B U L L H O R N
Staffing and Recruiting Software, On Target, On Demand
33-41 Farnsworth Street, 5th Floor, Boston, MA 02210
617-478-9126 (main)
http://www.bullhorn.com

Post Reply