0

I'm using Hybris by SAP for a small project and almost got this down. Im trying to find the amount of Point of Service locations with 0 Orders in the past 7 days using Flexible Search.

Here is the HAC script i used: 

select count(*), {PointOfService.name} from {Order left join PointOfService on {Order.pointOfService} = {PointOfService.pk}} where {creationTime} >= '2019-10-01' GROUP by {PointOfService.name} order by count(*)

The script gives me the quanity of orders for each individual PointOfService but does not give me the PointOfService locations with '0' orders. I read that this is due to count(*) not providing NULL values. Does anyone know a way around this?

Jee Mok
  • 6,157
  • 8
  • 47
  • 80
HybrisNoob
  • 1
  • 1

1 Answers1

0

You have a order attribute in your where(creationTime) so if there is no order for a specific point of service you wont be able to see it. Something like that should work:

select count(*),
{ps.name}
from {PointOfService as ps 
left join Order as o on {o.pointOfService} = {ps.pk}}
where count({o.pk}) == 0
GROUP by {ps.name}
Djamaster
  • 29
  • 1
  • 7