How to create a Computation with the Advanced mode in SQL?

  • Updated on Mar 16, 2026
  • Published on Oct 17, 2024
  • 4 minute(s) read
Prev Next

When creating a Computation you may need to write it in SQL through the Advanced mode when the Conditional does not allow you to write the logic you would like. Check this article to find out what SQL structure is recommended! 

Prerequisites

  • You are a HG Insights user with Admin or Architect permissions

  • You know what Computations are for 

  • You know basic SQL coding

In the page to create a computation, select Advanced for the Mode of Configuration. You'll see an empty block where you can write SQL logic to create a computation. 

Objects Used in Advanced Computation SQL Scripts

When creating advanced computations in SQL, you can utilize two primary object/sources for your inputs:

  • Attributes from Attribute Mapping: These are the fields that you have mapped from your CRM or other data sources, without value edits. Please don’t use the Salesforce to call on these datapoints, as computations cannot directly rely on your integrations fields (they need to be transformed into RGIP '“attributes”). The exact names for these attributes can be found on the attribute mapping page in the admin app, specifically in the right-hand column. (see below screenshot)

  • Other Computations: You can also reference Standard Computations or computations that you have previously defined. The naming convention for these computations can be found in the URL when you are in edit mode in the studio. This enables you to build upon existing logic and create more complex computations (see below)

By leveraging these two sources, you can create powerful and flexible SQL computations that enhance your models or playbooks capabilities. Make sure to follow the naming conventions to avoid any errors in your SQL scripts!

List of valid logical statements below 

CASE WHEN..THEN..ELSE..END

A CASE statement allows writing a logic "IF this condition is verified THEN output1 ELSE output2", where the output can be a value or a field. 

  • A condition can include AND and OR. 

  • Format: 

    • Important:

      • the order of the WHENs is important: the output for a record will correspond to the first WHEN condition verified

      • The output must be of the same type of the computation 

        • The output for a boolean computation would be 1 = true or 0 = false or NULL

        • The output for a numeric computation would be an integer or NULL 

        • The output for a string computation needs to be within ' '  or NULL

      • numeric computations using attributes or other computation should use a CAST ( field AS NUMERIC) to make sure the fields are compatible. 

CASE 
  WHEN [condition1] THEN [output1]
  WHEN [condition2] THEN [output2] 
  ...
  WHEN [conditionN] THEN [outputN]
ELSE [outputDefault]
END

  • Example of a numeric computation combining HG Insights's enrichment and your Salesforce enrichment, where HG Insights's source is prioritized and your Salesforce data as a fallback. 

CASE 
   WHEN CAST(employees AS NUMERIC) >0 THEN CAST(employees AS NUMERIC)
   ELSE CAST(employees_salesforce AS NUMERIC)
END

  • Example of a string computation combining HG Insights's enrichment and your Salesforce enrichment, where HG Insights's source is prioritized and your Salesforce data as a fallback.

CASE 
    WHEN LOWER(industry) NOT IN ('unknown', 'cbit_unknown') THEN industry 
    ELSE industry_salesforce 
END

  • Other example of a string computation 

    • the ELSE can also be the value NULL

    • note the CAST in NUMERIC when using a numerical condition

CASE 
WHEN industry_salesforce IN ('Insurance') AND CAST (employees_salesforce AS NUMERIC) > 1000 THEN 'Large Insurance Company'
WHEN industry_salesforce IN ('Insurance') AND CAST (employees_salesforce AS NUMERIC) < 1000 AND CAST (employees_salesforce AS NUMERIC) > 100 THEN 'Medium Insurance Company'
WHEN industry_salesforce IN ('Insurance') AND CAST (employees_salesforce AS NUMERIC) < 100 AND CAST (employees_salesforce AS NUMERIC) > 0 THEN 'Small Insurance Company'
ELSE 'unknown'
END

  • Example of a boolean computation

    • the function LOWER() allows to not take into account LOWER/UPPER cases in the valuesCASE

WHEN lower(pers_title) like '%tech%' THEN 1
WHEN lower(pers_title) like '%data%' THEN 1
WHEN lower(pers_title) like '%analytics%' THEN 1
WHEN (pers_title  like '% IT%' and lower(pers_title) not like '%military%') THEN 1
WHEN lower(pers_title) like '%information%' THEN 1
WHEN pers_title like '% CIO%' THEN 1
ELSE 0
END

  • Example of a boolean computation

CASE 
WHEN salesforce_account_is_target IS NULL THEN NULL
WHEN salesforce_account_is_target = 'true' THEN 1
WHEN salesforce_account_is_target = 'false' THEN 0
ELSE 0
END

mceclip0.png

 

COALESCE

The COALESCE function allows to write a logic "IF field1 is null THEN use the field2 instead, IF field2 is null THEN use field3 instead...etc". 

Warning: COALESCE does not work if the null value of the field = "unknown" for example.

e.g if employee= 'unknown' and employees_salesforce = 100, then COALESCE (employee, employees_salesforce) would return 'unknown' instead of 100. -> In doubt use a CASE WHEN statement (see above) to explicit the null value. 

  • Format: 

COALESCE (field1, field2, ..., fieldN)

  • Example: you would like to create a computation for the Company size combining both the enrichment you have on the number of employees sitting in Salesforce and HG Insights's enrichment on the company. You would want to use the following computation.
    Warning: the order of the fields in the COALESCE is important.
     mceclip1.png
    Where  

    • employees_salesforce = computation from your Salesforce containing the information on the company size 

    • employees = computation from HG Insights's source containing the information on the company size  

CONCATENATION

You might need to use a concatenation operator in an advanced computation.

  • Format: 

field1 || field2 || field3

  • Example: you would like to concatenate the name of the integrations your leads are using

CASE WHEN LOWER(tech) LIKE '%salesforce%' OR LOWER(tech) LIKE '%salesforce%' THEN 'Salesforce; ' ELSE '' END 
|| CASE WHEN LOWER(tech) LIKE '%marketo%' OR LOWER(tech) LIKE '%marketo%' THEN 'Marketo; ' ELSE '' END
|| CASE WHEN LOWER(tech) LIKE '%amplitude%' OR LOWER(tech) LIKE '%amplitude%' THEN 'Amplitude' ELSE '' END

The results would be:

"Salesforce; " when only Salesforce is detected

"Marketo; " when only Marketo is detected

"Salesforce; Marketo; Amplitude" when all 3 techs are detected

"Salesforce; Amplitude" when Salesforce and Amplitude are detected

"" when nothing is detected

 

F.A.Q.

What other SQL functions can I use in advanced mode?

Our advanced mode for creating computations only supports functions that are compatible with both RedShift SQL and SQLite.

Need help? Open a ticket with our support team

Related articles