using-decode-in-sql-join-predicate.md

DECODE in SQL JOIN predicate

DECODE is powerful Snowflake function that can be used to implement if..then.elseif..then..endif. It can be used in a SQL JOIN predicate as well as following

Query

select 
  contact_email
  , product_id
  , name
from sat_lead_salesforce
inner join sat_opportunity_salesforce 
  on decode(
    primary_email_num, 
    1, email_1, 
    2, email_2, 
    3, email_3
  ) = sat_opportunity_salesforce.contact_email;

Query Output

OPPORTUNITY_ID	PRODUCT_ID	CONTACT_EMAIL
1	1111	[email protected]
2	2222	[email protected]
3	1111	[email protected]
4	1111	[email protected]

Raw Data

sat_lead_salesforce

CONTACT_ID	EMAIL_1	EMAIL_2	EMAIL_3	PRIMARY_EMAIL_NUM	NAME
1	saqib_one@…	saqib_two@…	saqib_three@…	2	Saqib Ali
2	scott_one@…	scott_two@…	scott_three@…	3	Scott
3	ben_one@…	ben_two@…	ben_three@…	2	Ben

sat_opportunity_salesforce

OPPORTUNITY_ID	PRODUCT_ID	CONTACT_EMAIL
1	1111	[email protected]
2	2222	[email protected]
3	1111	[email protected]
4	1111	[email protected]

Screenshot(s)

See also

{% for page in site.pages %} {% if (page.title contains "DECODE") %}
• {{ page.title | escape }}
{% endif %} {% endfor %}