How to get group by data based on customTextBlock in rest API service

Forum for users and developers of Bullhorn's API service.

Moderators: StaffingSupport, s.emmons, BullhornSupport

Post Reply
haribullhorn
User
Posts: 2
Joined: Tue Jul 07, 2020 11:27 am

How to get group by data based on customTextBlock in rest API service

Post by haribullhorn »

Hi,

Group by for customTextBlock5, I did same as category but this is not working.

Request URL:https://public-rest777.bullhornstaffing ... t?where=id IN (47236,50802,70844,86360,104347,111318,112002,112544,113523,115239)&groupBy=customTextBlock5&fields=customTextBlock5,count(id)&count=500&orderBy=+customTextBlock5,-count.id&start=0
Request method:GET
Status code: 400

{
"errorMessage": "javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query",
"errorCode": 400
}

I have tried group by as following sql as well
SELECT count(jobOrderID)

-- ,[customTextBlock5]

FROM [TTTT].[dbo].[JobOrder]
where [TTTT].[dbo].[JobOrder].[jobOrderID] in (47236,50802,70844,86360,104347,111318,112002,112544,113523,115239)

group by [TTTT].[dbo].[JobOrder].[customTextBlock5]


I analyzed that customTextBlock5 data type is text in DB and you cannot make group by on text data type. I ran following and its working

SELECT count(jobOrderID)

-- ,[customTextBlock5]

FROM [BHDMQA].[dbo].[JobOrder]
where [BHDMQA].[dbo].[JobOrder].[jobOrderID] in (47236,50802,70844,86360,104347,111318,112002,112544,113523,115239)

group by CAST([BHDMQA].[dbo].[JobOrder].[customTextBlock5] AS NVARCHAR(MAX))


In career portal code as follows:
assembleForGroupByWhereIDs: function assembleForGroupByWhereIDs(fields, orderByFields, start, count, jobs) {

return '?where=' + _this3.requestParams.whereIDs(jobs, false) + '&groupBy=' + fields + '&fields=' + fields + ',count(id)&count=' + count + '&orderBy=+' + orderByFields + ',-count.id&start=' + start;

},

So, I want to know how will make group by for customTextBlock5

Based on this issue I am unable to make sidebar for this customTextBlock5 i.e Brand offering in career portal(angularjs)

Thanks,
Hari

bbagwell
User
Posts: 16
Joined: Wed Feb 26, 2020 12:17 pm

Re: How to get group by data based on customTextBlock in rest API service

Post by bbagwell »

I just attempted this and had the same issue. I'm researching this now and will post a reply as soon as I have an update for you.

haribullhorn
User
Posts: 2
Joined: Tue Jul 07, 2020 11:27 am

Re: How to get group by data based on customTextBlock in rest API service

Post by haribullhorn »

Any solution ??

I am waiting solution for this because based on this I need to complete my project.
Please provide me solution.

kschutten
User
Posts: 23
Joined: Tue Jun 25, 2019 2:22 pm

Re: How to get group by data based on customTextBlock in rest API service

Post by kschutten »

Hi,

Not familiar with the Career Portal, but as far as I know there is no groupBy/aggregate function (directly) on Bullhorn API.

Post Reply