HAVING Clause in Query JSON with SQL

💬 Queries and Resources
,
1
  1. My goal:

We're trying to join data from different SQL queries in a Query JSON with SQL resource, using the formatDataAsArray methods and AlaSQL to match the data. We've used this resource quite a few times and are familiar with the common pitfalls (e.g. case sensitivity)

  1. Issue:

About a week or so ago, we noticed a syntax error in queries that had a HAVING clause - removing the having clause seemed to fix the issue. No other changes to the underlying resources seems to have occurred.

  1. Steps I've taken to troubleshoot:
    Updated the having clause to have a dummy condition, used aliases, moved the condition from a having to a CTE and where clause seemed to be the only way that worked.
2

Welcome to the community, @DennyK! Are you experiencing this issue on a self-hosted instance? If so, which version? Can you also clarify whether this is happening in an app or workflow?

3

Thanks for the reply. I do not believe it's on a self-hosted instance. This is happening in an app, not a workflow.

4

SELECT A,
SUM(B) [Quantity]
FROM {{ formatDataAsArray({
"A" : [1,1,1,2,2],
"B" : [10,1,1,0,1]
}) }} a
GROUP BY A
HAVING COUNT(1) > 1

Running this in a Query JSON with SQL gives me a syntax error

  • message:"s is not defined"

Running it withouth the HAVING clause returns results, which I can then manually filter with a where clause

5

Yep, I see the same thing and can confirm that the query does run in older versions. Something similar happened the last time we bumped the version of our AlaSQL library, so I imagine tracking down the breaking change will be fairly straightforward. I'll provide an update as soon as I have news to share!

For anybody else tracking this issue, the above workaround will keep your query running as it should. :+1:

1 Like