Page 1 of 1

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

Posted: Tue Jul 07, 2020 11:30 am
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

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

Posted: Tue Jul 14, 2020 1:03 pm
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.

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

Posted: Thu Jul 30, 2020 10:55 am
by haribullhorn
Any solution ??

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

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

Posted: Mon Aug 10, 2020 1:45 pm
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.