Using IN Operator in Nested Select in NRQL

I have two different custom events.

  • prod:order:OrderStatusUpdated
  • prod:tenant:StoreStatusChanged

I would like to know the storeNumber that exists in prod:tenant:StoreStatusChanged event, but does not exist in prod:order:OrderStatusUpdated.

The expected result in this example:

  • StoreNumber 3

How can I do that?

Please check the printscreens below:

enter image description here
enter image description here

No option for joins etc, but you can get close by doing like this:

Select Filter(count(*), where eventType() = "prod:tenant:StoreStatusChanged") as SSC, Filter(count(*), where eventType() = "prod:tenant:OrderStatusChanged") as OSC from prod:tenant:StoreStatusChanged, prod:tenant:OrderStatusChanged where tenantName - "123" facet storeNumber since 3 days ago limit max

Use that and look for empty values.

I’d maybe suggest taking a different approach with your custom events and use attributes to describe if the event is an order or store status change etc. Not sure how many custom events you have, but making the event type too granular can lead to some difficult to use NRQL.

You could have an event named TenantEvent and have environment, store number, changeType, orderNumber etc and then facet on more than one attribute…

3 Likes

Let us know how that suggestion works out for you @jfreitas!

1 Like

It works, thank you, I couldn’t come up with this solution without your help :sunglasses:

1 Like