I need to get a list of companies and the number of tickets for each company. So if a customer has null records, they still need to come back in the query.
I set up my database view like below, when I click the "try it" link, the data displays as it should:
Now when I try to use this database view in a GlideAggretate query, it is excluding the customers that have no records, which I need.
var grCustNoAE = new GlideAggregate("u_customer_task_leftjoin");
grCustNoAE.addAggregate("COUNT");
grCustNoAE.addEncodedQuery("corecompany_u_invalid=false^corecompany_u_account_executiveISEMPTY^corecompany_customer=true^corecompany_u_prospect=true^tsk_numberSTARTSWITHINC^ORtsk_numberSTARTSWITHTKT^ORtsk_numberSTARTSWITHREQ");
grCustNoAE.groupBy('corecompany_sys_id');
grCustNoAE.query();
var x = 0;
while(grCustNoAE.next()){
gs.info('CustSysID = ' + grCustNoAE.corecompany_sys_id + ' - CustName = ' + grCustNoAE.corecompany_name.toString() + ' - ' + grCustNoAE.getAggregate('COUNT'));
}When I parse out the query that is displayed by the system, it forces the query to an inner join, even though the database view is set up as a left join.
SELECT
corecompany.`sys_id` AS `corecompany_sys_id`
, count(*) AS recordcount
FROM
(task tsk
INNER JOIN
core_company corecompany
ON
corecompany.`sys_id` = tsk.`company`
)
WHERE (tsk.`number` LIKE 'INC%' OR tsk.`number` LIKE 'TKT%' OR tsk.`number` LIKE 'REQ%')Is it possible to write a left join GlideAggregate query in ServiceNow?
