Details
Major Description
I'm getting an "Internal Error" through the Analytics workbench and I'm not sure why. I suspect it's a syntax error with my query, but it just says generic "Internal Error' code 25000 and then prints out the whole query.
- I had two versions of a query, both of which were working
- I added this line to both of them:
and audit.user not in ["perry@couchbase.com", "georgina.martin@couchbase.com", "sulemaan.yasin@couchbase.com", "zain.baig@couchbase.com"]
- It worked in one of them and not the other.
- To get it working in the other one I had to put the not before the field:
and not audit.user in ["perry@couchbase.com", "georgina.martin@couchbase.com", "sulemaan.yasin@couchbase.com", "zain.baig@couchbase.com"]The full query that is not working:
SET `compiler.parallelism` "8";
|
SELECT opp_group.a.account_id,
|
opp_group.a.name AS account_name,
|
opp_group.a.customer_type,
|
opp_group.a.owner_email,
|
opp_group.o.stage,
|
REGEXP_REPLACE(IFMISSINGORNULL(opp_group.a.region, opp_group.a.owner_role), "(^!| Sales Team$| SDR$)", "") AS region,
|
opp_group.o.name AS opportunity_name,
|
opp_group.o.opportunity_type,
|
opp_group.o.close_date AS opportunity_close_date,
|
opp_group.id,
|
opp_group.amount,
|
SUM(opp_group.total_cnt) AS c360_accesses,
|
SUM(opp_group.total_cnt)*amount as weight
|
FROM (
|
SELECT month_group.a,
|
month_group.o,
|
month_group.month,
|
month_group.id as id,
|
month_group.amount as amount,
|
SUM(month_group.cnt) AS total_cnt,
|
CASE WHEN SUM(month_group.cnt) = 0 THEN [] ELSE ARRAY_AGG({ month_group.user, month_group.cnt }) END AS user_accesses
|
FROM (
|
SELECT a,
|
o,
|
meta(o).id as id,
|
r.source.Amount as amount,
|
SUBSTR(date_full, 0, 7) AS month,
|
audit.user,
|
COUNT(audit) AS cnt
|
FROM opportunities o
|
JOIN opportunities_with_revenue r on meta(r).id = meta(o).id
|
JOIN accounts a ON o.account_id = a.account_id
|
UNNEST DATE_RANGE_STR(DATE_ADD_STR(SUBSTR(o.close_date, 0, 10), -1, "year"), DATE_ADD_STR(SUBSTR(o.close_date, 0, 10), 1, "month"), "month") AS date_full LEFT
|
JOIN customer_page_audit audit ON SPLIT(audit.uri, "/")[2] = a.account_id
|
AND SUBSTR(audit.datetime, 0, 7) = SUBSTR(date_full, 0, 7)
|
WHERE SUBSTR(o.close_date, 0, 10) BETWEEN "2021-05-01" AND "2021-07-31"
|
and o.stage like "10%"
|
and audit.user not in ["perry@couchbase.com", "georgina.martin@couchbase.com", "sulemaan.yasin@couchbase.com", "zain.baig@couchbase.com"]
|
GROUP BY a,
|
o,
|
r.source.Amount,
|
SUBSTR(date_full, 0, 7),
|
audit.user,
|
audit.uri,meta(o).id) month_group
|
GROUP BY month_group.a,
|
month_group.o,
|
month_group.r,
|
month_group.month,
|
month_group.id,
|
month_group.amount
|
Order BY month_group.month) opp_group
|
where total_cnt > 0
|
GROUP BY opp_group.a,
|
opp_group.o,
|
opp_group.r,
|
opp_group.id,
|
opp_group.amount
|
ORDER BY SUM(opp_group.total_cnt) DESC;
|