---
title: "How to create a Computation with the Advanced mode in SQL?"
slug: "how-to-create-a-computation-with-the-advanced-mode-in-sql"
updated: 2026-03-16T13:09:24Z
published: 2026-03-16T13:09:24Z
canonical: "help.madkudu.com/how-to-create-a-computation-with-the-advanced-mode-in-sql"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://help.madkudu.com/llms.txt
> Use this file to discover all available pages before exploring further.

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

[When creating a Computation](https://support.madkudu.com/hc/en-us/articles/360058926432-How-to-create-a-computation-in-MadKudu-s-Data-Science-Studio-) 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](https://support.madkudu.com/hc/en-us/articles/4403777900301-What-are-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)

![](https://cdn.document360.io/a55e7ea5-b8ac-4456-874d-10cc92097370/Images/Documentation/Screenshot 2026-03-16 at 13.46.21.png)
- **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)![](https://cdn.document360.io/a55e7ea5-b8ac-4456-874d-10cc92097370/Images/Documentation/Screenshot%202026-03-16%20at%2013.58.27.png)

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.*

```Custom
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.**

```Custom
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.**

```Custom
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

```Custom
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

```Custom
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

```Custom
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](https://support.madkudu.com/hc/article_attachments/4423695821965)

# **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:

```Custom
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](https://support.madkudu.com/hc/article_attachments/4423679845389) 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:

```Custom
field1 || field2 || field3
```

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

```Custom
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](https://portal.usepylon.com/hg-insights/forms/submit-a-request)

enrichment available out of the box, provided by MadKudu