error "Unsupported subquery type cannot be evaluated" in snowflake

Question

I am seeing the following error when I run the below code.

Unsupported subquery type cannot be evaluated

When I comment the third select statement and run the query, the query is running with the result. This error is popping up at the last select statement.

Can I know how do I put the last select statement in my query?

SELECT "A1" as CUT
       ,"B1" as SERV
       ,"c1" as NAME
       ,"D1"
       ,"E1" as REP 
       ,(SELECT COUNT(ACT2."B1") 
         FROM AAA as ACT2 
         WHERE ACT2."AST" in ('A', 'T') 
            and ACT2."UCE" = 'IN' 
            and ACT2."CUST" = ACT."CUST") as NCount
       ,(SELECT ACT2."B1" 
         FROM AAA as ACT2 
         WHERE ACT2."AST" in ('A', 'T') 
            and ACT2."UCE" = 'IN' 
            and ACT2."CUST" = ACT."CUST" 
         LIMIT 1) as INTER
FROM BBB
left outer join AAA as ACT 
   on "WNB" = ACT."B1" 
WHERE "PCD" in ('PD', 'PD2') 
   and "PDT" = 0 
   and ACT."AST" in ('A', 'T')

Simeon Pilgrim Simeon Pilgrim · Accepted Answer · 2019-10-05T01:00:07

Without having run this code given you are joining to thing you could use a CTE. Given the lack of sort of b1 ANY_VALUE can be used.

WITH inters AS (
    SELECT cust
        ,ANY_VALUE(b1) AS b1
    FROM AAA
    WHERE ast IN ('A', 'T') AND uce = 'IN' 
    GROUP BY cust
)           
SELECT a1 AS cut
    ,b1 AS serv
    ,c1 AS name
    ,d1
    ,e1 AS rep 
    ,(SELECT COUNT(ACT2."B1") FROM AAA as ACT2 WHERE ACT2."AST" in ('A', 'T') and ACT2."UCE" = 'IN' and ACT2."CUST" = ACT."CUST") as NCount
    ,i.b1 AS INTER
FROM BBB
LEFT JOIN aaa AS act 
    ON wnb = act.b1 
LEFT JOIN inters AS i
    ON i.cust = act.cust
WHERE pcd IN ('PD', 'PD2') 
    AND pdt = 0 
    AND act.ast IN ('A', 'T')

but the first sub select is using the same source table and filters as the seconds so that can also be moved to the CTE as:

WITH inters AS (
    SELECT cust
        ,ANY_VALUE(b1) AS b1
        ,COINT(b1) AS ncount
    FROM AAA
    WHERE ast IN ('A', 'T') AND uce = 'IN' 
    GROUP BY cust
)           
SELECT a1 AS cut
    ,b1 AS serv
    ,c1 AS name
    ,d1
    ,e1 AS rep 
    ,i.ncount
    ,i.b1 AS INTER
FROM BBB
LEFT JOIN aaa AS act 
    ON wnb = act.b1 
LEFT JOIN inters AS i
    ON i.cust = act.cust
WHERE pcd IN ('PD', 'PD2') 
    AND pdt = 0 
    AND act.ast IN ('A', 'T')

which should work better that the first code.

depending how large the aaa table is there might be some performance gain from adding another CTE which filters on ast IN ('A', 'T') which is then used by the inters CTE and the main SELECT as those will both read the same table twice. But this aspect more like will be impacted by the volume of your data that meets the other constraints.

error "Unsupported subquery type cannot be evaluated" in snowflake

1 Answers