Analytical service Query alias not working

Analytics
1

I have query it is working in Query editor and having issue with Analytical service.

SELECT ev.detourId AS detourId, date_part_str(ev.upsertDate, “year”) AS year_1, date_part_str(ev.upsertDate, “month”) AS month_1,
SUM(CASE WHEN ev.event = “OPEN” THEN 1 ELSE 0 END) AS open_cnt,
SUM(CASE WHEN ev.event = “PRESENTED” THEN 1 ELSE 0 END) AS presented_cnt,
SUM(CASE WHEN ev.event = “COMPLETED” THEN 1 ELSE 0 END) AS completed_cnt
FROM entdetcb AS d
UNNEST d.events1 AS ev
WHERE d.docType= “testRec”
AND ev.detourId = ‘xyz123’
AND ISOBJECT(d.events1) = FALSE
AND ev.upsertDate > DATE_TRUNC_STR(DATE_ADD_STR(CLOCK_LOCAL(), -365, ‘day’) , ‘month’)
GROUP BY ev.detourId,
DATE_PART_STR(ev.upsertDate, “year”),
DATE_PART_STR(ev.upsertDate, “month”)

I’m getting err Cannot resolve alias reference for undefined identifier ev (in line 1, at column 47) for field “ev.upsertDate”

2

Hi @rajv,

This looks like a bug in the query compiler. The problem is that date_part_str function name is written in lower case in the SELECT cause, but in upper case in the GROUP BY clause. As a workaround, use the same case in both SELECT and GROUP BY clauses:

SELECT … DATE_PART_STR(ev.upsertDate, “year”) …
GROUP BY DATE_PART_STR(ev.upsertDate, “year”) …