I have a small query running in RedShift through Aginity that is getting the following error:
ERROR: XX000: This type of correlated subquery pattern is not supported due to internal error
Current Query:
SELECT 'MYSCHEMA.MYTABLE.FIELDA' as obj_name,
COUNT(*) as rows,
COUNT(DISTINCT FIELDA) as distinct_vals,
SUM(CASE WHEN FIELDA in ('00DK','00DC','00DE','00DD','00DB') THEN 1 ELSE 0 END) as enter,
SUM(CASE WHEN TRIM(FIELDA) IN ('',null) THEN 1 ELSE 0 END) as nulls
FROM MYSCHEMA.MYTABLE
However if i remove the DISTINCT it runs:
SELECT 'MYSCHEMA.MYTABLE.FIELDA' as obj_name,
COUNT(*) as rows,
COUNT(FIELDA) as distinct_vals,
SUM(CASE WHEN FIELDA in ('00DK','00DC','00DE','00DD','00DB') THEN 1 ELSE 0 END) as enter,
SUM(CASE WHEN TRIM(FIELDA) IN ('',null) THEN 1 ELSE 0 END) as nulls
FROM MYSCHEMA.MYTABLE
Does it work if you use window functions in a subquery?
SELECT 'MYSCHEMA.MYTABLE.FIELDA' as obj_name,
COUNT(*) as rows,
SUM( (seqnum = 1)::INT ) as distinct_vals,
SUM(CASE WHEN FIELDA in ('00DK','00DC','00DE','00DD','00DB') THEN 1 ELSE 0 END) as enter,
SUM(CASE WHEN TRIM(FIELDA) IN ('',null) THEN 1 ELSE 0 END) as nulls
FROM (SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY FIELDA ORDER BY FIELDA) as seqnum
FROM MYSCHEMA.MYTABLE t
) t