Unsupported subquery in snowflake

Question

I am trying to run a query in snowflake:

SELECT DISTINCT RPU.RID,
RA.DATE,
COALESCE(RTH.CT, 0) as RCNT,
COALESCE (R.COMMENT_SITE, PR.COMMENT_SITE) as COMMENT_SITE,

##########################
COALESCE ((SELECT SPD FROM "DB"."SCHEMA"."PRD" WHERE
           COMMENT_SITE = R.COMMENT_SITE AND CLOTH_ID = RPU.GPR
           ORDER BY (IS_ACTIVE = false) LIMIT 1), PR.SPD) as SPD
#########################
FROM "DB"."SCHEMA"."CLOTH_USED" RPU JOIN "DB"."SCHEMA"."CAGR" RA ON RPU.RID = RA.RID 
LEFT OUTER JOIN "DB"."SCHEMA"."PNTR_IMP" PR ON RPU.COMMENT_ID[1] = PR.ID
left outer join "DB"."SCHEMA"."COMMENTS" R ON RPU.COMMENT_ID[1] = R.ID
LEFT OUTER JOIN CLOTH_COUNT RTH ON RTH.CLOTH_ID = RPU.GPR 
ORDER BY RPU.RID DESC, COALESCE(RTH.CT, 0) ASC, RA.DATE DESC LIMIT 1;

I get the error as:

SQL compilation error: Unsupported subquery type cannot be evaluated

The following query works fine:

SELECT DISTINCT RPU.RID,
RA.DATE,
COALESCE(RTH.CT, 0) as RCNT,
COALESCE (R.COMMENT_SITE, PR.COMMENT_SITE) as COMMENT_SITE,
FROM "DB"."SCHEMA"."CLOTH_USED" RPU JOIN "DB"."SCHEMA"."CAGR" RA ON RPU.RID = RA.RID 
LEFT OUTER JOIN "DB"."SCHEMA"."PNTR_IMP" PR ON RPU.COMMENT_ID[1] = PR.ID
left outer join "DB"."SCHEMA"."COMMENTS" R ON RPU.COMMENT_ID[1] = R.ID
LEFT OUTER JOIN CLOTH_COUNT RTH ON RTH.CLOTH_ID = RPU.GPR 
ORDER BY RPU.RID DESC, COALESCE(RTH.CT, 0) ASC, RA.DATE DESC LIMIT 1;

The issue SQL compilation error: Unsupported subquery type cannot be evaluated occurs when I add:

COALESCE ((SELECT SPD FROM "DB"."SCHEMA"."PRD" WHERE
           COMMENT_SITE = R.COMMENT_SITE AND CLOTH_ID = RPU.GPR
           ORDER BY (IS_ACTIVE = false) LIMIT 1), PR.SPD) as SPD

How can I correct the query? The problem is with where clause, without where it works fine. How do I re-write the query?

Answer 1

So correlated sub queries can normally be turn into some form of join in you case the primary things you are doing is matching on two columns and selecting what appears randomly (if you have more than 1 row in active=false state) a row, this can be turned into a row_number() and using QUALITY as the equivalent as LIMIT.

Thus:

WITH sub_query AS (
    SELECT 
        spd
        comment_site
        cloth_id
    FROM db.schema.prd
    QUALIFY row_number() OVER (PARTITION BY comment_site, cloth_id ORDER BY IS_ACTIVE = false) = 1
)
SELECT DISTINCT 
    rpu.rid,
    ra.date,
    COALESCE(rth.ct, 0) AS rcnt,
    COALESCE (r.comment_site, pr.comment_site) AS comment_site,

    COALESCE ( sq.spd, pr.spd) as SPD

FROM DB.schema.cloth_used AS rpu 
JOIN db.schema.cagr  AS ra 
    ON rpu.rid = ra.rid 
LEFT OUTER JOIN db.schema.pntr_imp AS pr 
    ON rpu.comment_id[1] = pr.id
LEFT OUTER JOIN db.schema.comments AS r 
    ON rpu.comment_id[1] = r.id
LEFT OUTER JOIN sub_query AS sq
    ON sq.comment_site = r.comment_site AND sq.cloth_id = rpu.gpr
LEFT OUTER JOIN cloth_count AS rth 
    ON RTH.CLOTH_ID = RPU.GPR 
ORDER BY rpu.rid DESC, COALESCE(rth.ct, 0) ASC, ra.date DESC 
LIMIT 1;

should meet you needs, now I used a CTE but that can be moved into the JOIN region if you prefer, or need like so:

SELECT DISTINCT 
    rpu.rid,
    ra.date,
    COALESCE(rth.ct, 0) AS rcnt,
    COALESCE (r.comment_site, pr.comment_site) AS comment_site,

    COALESCE ( sq.spd, pr.spd) as SPD

FROM DB.schema.cloth_used AS rpu 
JOIN db.schema.cagr  AS ra 
    ON rpu.rid = ra.rid 
LEFT OUTER JOIN db.schema.pntr_imp AS pr 
    ON rpu.comment_id[1] = pr.id
LEFT OUTER JOIN db.schema.comments AS r 
    ON rpu.comment_id[1] = r.id
LEFT OUTER JOIN (
    SELECT 
        spd
        comment_site
        cloth_id
    FROM db.schema.prd
    QUALIFY row_number() OVER (PARTITION BY comment_site, cloth_id ORDER BY IS_ACTIVE = false) = 1
) AS sq
    ON sq.comment_site = r.comment_site AND sq.cloth_id = rpu.gpr
LEFT OUTER JOIN cloth_count AS rth 
    ON RTH.CLOTH_ID = RPU.GPR 
ORDER BY rpu.rid DESC, COALESCE(rth.ct, 0) ASC, ra.date DESC 
LIMIT 1;