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?
x
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.
Advertisement
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