Single-row subquery returns more than one row in snowflake

Single-row subquery returns more than one row.

This is the error that I get while running the following snowflake code. I am aware that it is because of the TO_VARCHAR function is not for a whole table column..any idea on how to do workaround and have it applied to the whole column?

SELECT sv.SKU
       , li.price
       , li.cost_price
       , pc.product_description
FROM raw.stitch_heroku.spree_line_items as li 
LEFT OUTER JOIN (SELECT * 
                 FROM raw.stitch_heroku.spree_variants 
                 WHERE LENGTH(raw.stitch_heroku.spree_variants.SKU) Between 4 and 5) as sv
ON li.variant_id = sv.id
LEFT JOIN (SELECT analytics.dbt_lcasucci.product_category.product_description
                  , TO_VARCHAR(analytics.dbt_lcasucci.product_category.product_ID) 
           FROM analytics.dbt_lcasucci.product_category) as pc
ON (SELECT TO_VARCHAR(analytics.dbt_lcasucci.product_category.product_ID) 
    FROM analytics.dbt_lcasucci.product_category) = sv.id
GROUP BY  sv.SKU,li.price,li.cost_price,pc.product_description

I have to change the data type of the table called analytics.dbt_lcasucci.products from int to varchar, since without that the join will not recognize those fields and will just print null values even if the actual content of the cells would be matching.

Answer

You have a wrong subquery in a ON clause ..

SELECT sv.SKU
    , li.price
    , li.cost_price
    , pc.product_description
FROM raw.stitch_heroku.spree_line_items as li 
LEFT OUTER JOIN ( 
    SELECT * 
    FROM raw.stitch_heroku.spree_variants 
    WHERE LENGTH(raw.stitch_heroku.spree_variants.SKU) Between 4 and 5
) as sv ON li.variant_id = sv.id
LEFT JOIN ( 
    SELECT analytics.dbt_lcasucci.product_category.product_description
    , TO_VARCHAR(analytics.dbt_lcasucci.product_category.product_ID) my_key
    FROM analytics.dbt_lcasucci.product_category 
    ) as pc  ON pc.my_key = sv.id

and not having aggreagtion function .. you don’t need group by ..eventually use distinct if you don’t want duplicated rows

ON (SELECT TO_VARCHAR(analytics.dbt_lcasucci.product_category.product_ID) 
    FROM analytics.dbt_lcasucci.product_category) = sv.id