CODEWITHSUNDEEP
sqljointypesleft-joinsnowflake-cloud-data-platform
lcasucci
lcasucci

Reputation: 87

Snowflake not recognizing a CAST or to TO_VARCHAR function over a join key

I am trying to perform a join across three tables. This is the initial code, that works but returns all NULL for product description. I figured it is because in the first 2 tables that I am joining the joining key is the varchar type while in the other it is an INT. The int key is analytics.dbt_lcasucci.product_category.product ID (last join).

SELECT raw.stitch_heroku.spree_line_items.variant_id
       , raw.stitch_heroku.spree_variants.SKU
       , raw.stitch_heroku.spree_line_items.price
       , raw.stitch_heroku.spree_line_items.cost_price
       , pc.product_description
FROM raw.stitch_heroku.spree_line_items 
LEFT OUTER JOIN raw.stitch_heroku.spree_variants 
ON raw.stitch_heroku.spree_line_items.variant_id = raw.stitch_heroku.spree_variants.id
LEFT JOIN analytics.dbt_lcasucci.product_category as pc ON pc.product_ID = raw.stitch_heroku.spree_variants.id
GROUP BY raw.stitch_heroku.spree_line_items.variant_id
      , raw.stitch_heroku.spree_variants.SKU
      , raw.stitch_heroku.spree_line_items.price
      , raw.stitch_heroku.spree_line_items.cost_price
      , pc.product_description

Next, I tried to use some snowflake function to convert the analytics.dbt_lcasucci.product_category.product ID into a varchar.

This is what I tried :

LEFT JOIN (
   SELECT analytics.dbt_lcasucci.product_category.product_description
          , T0_VARCHAR(analytics.dbt_lcasucci.product_category.product_ID) 
   FROM analytics.dbt_lcasucci.product_category) as pc
   ON pc.product_ID= sv.id

or 

LEFT JOIN (
   SELECT analytics.dbt_lcasucci.product_category.product_description
   , TRY_CAST(analytics.dbt_lcasucci.product_category.product_ID as VARCHAR(10) 
   FROM analytics.dbt_lcasucci.product_category) as pc
   ON pc.product_ID= sv.id

The error I have been getting is : Invalid identifier T0_VARCHAR or Function TRY_CAST cannot be used with arguments of types NUMBER(38,0) and VARCHAR(10)

I am not sure if there is a way to fix it by doing this or if I just should re upload that file changing the type at its source.

Thank you in advance!!!!

Upvotes: 1

Views: 7498

Answers (1)

VBoka
VBoka

Reputation: 9083

When I look at your code I have to notice this:

T0_VARCHAR(analytics.dbt_lcasucci.product_category.product_ID) FROM

In this part of the code it seems that you have used TO_VARCHAR with number zero 0 and not with letter O ? Please check.

And for your second attempt I see here : https://docs.snowflake.net/manuals/sql-reference/functions/try_cast.html that you have to send a string as an argument for your TRY_CAST function.

Upvotes: 2

Related Questions