How to Use SQL Functions in Join Conditions Using Code View in Bold BI

This article shows you to how to edit the generated SQL in Code View to apply SQL functions inside a JOIN condition when two tables cannot be joined with a direct column-to-column match. It’s for advanced users who need to transform values (parse, trim, normalize) before joining.

Prerequisites

• Bold BI data source already created (Extract or Live), connected to your database.
• The required tables are available and added to the data model.
• An initial relationship (basic join) is already configured in Design View, so Bold BI generates a base JOIN query.
• You have permission to edit data sources in Bold BI (Designer/Admin role depending on your setup).
• You know which SQL functions are supported by your database engine (PostgreSQL, MySQL, SQL Server, etc.). Supported functions are database-specific.

Step-by-Step Instructions

Step 1: Create the required tables and a basic join in Design View

• Open the data source in the Bold BI Data Source Designer.
• Drag Table A and Table B into the designer canvas.
• Create a basic relationship (even if it’s not fully correct yet) to force Bold BI to generate a JOIN structure you can edit in Code View.
  
  
  
  
  
• Save (if needed).

Step 2: Open Code View

• In the datasource design page, enable the Code slider (tools pane) to open the Query Editor (Code View).
  
  
  
  
  

Step 3: Locate the generated JOIN clause

• In the Query Editor, find the FROM ... and JOIN ... ON ... sections.
  
  
• Confirm the table aliases Bold BI uses (for example, A and B, or generated aliases).

Tip: Always qualify columns with the table/alias name to avoid ambiguous column errors.

Step 4: Modify the ON condition using SQL functions

• Replace the direct equality join (example: A.UserName = B.Email) with a transformed join.
  
  
• Apply the SQL function(s) needed to make both sides comparable (for example, extract the username from an email, normalize case, remove spaces).
• Important performance note: Using functions in join conditions can reduce index usage and impact performance. Use this approach only when needed and test the query execution time.

Step 5: Save and preview the data source

• Click Run button to validate the query (and acknowledge the warning if shown).
  
  
  
  
  
• Click Update to preview the output grid.
• Click Save to save the data source definition.
  
  

After executing the modified query, the preview displays matching records successfully. The username portion extracted from the email address is compared against the UserName column using a case-insensitive JOIN condition.

Code Samples

Example: Join usernames to email addresses by extracting the username

Scenario

• Table A (Users): stores UserName (example: ericko)
• Table B (AuditLogins): stores Email (example: [email protected])
• You want to join them by matching Users.UserName to the email prefix (everything before @).
  PostgreSQL example (using split_part + lower)

SELECT
 u.UserId,
 u.UserName,
 a.Email,
 a.LastLoginUtc
FROM Users u
JOIN AuditLogins a
 ON lower(u.UserName) = lower(split_part(a.Email, '@', 1)); 

SQL Server example (using LEFT + CHARINDEX + LOWER)

SELECT
 u.UserId,
 u.UserName,
 a.Email,
 a.LastLoginUtc
FROM Users u
JOIN AuditLogins a
 ON LOWER(u.UserName) = LOWER(LEFT(a.Email, CHARINDEX('@', a.Email) - 1)); 

MySQL example (using SUBSTRING_INDEX + LOWER)

SELECT
 u.UserId,
 u.UserName,
 a.Email,
 a.LastLoginUtc
FROM Users u
JOIN AuditLogins a
 ON LOWER(u.UserName) = LOWER(SUBSTRING_INDEX(a.Email, '@', 1)); 

• Use the version that matches your database engine. Bold BI sends the query to your database, so the function availability and syntax are determined by the database.

Additional SQL Function Examples

• These examples show common patterns used to normalize/join “almost matching” fields. Supported functions depend on your database engine, so treat these as templates.

1. split_part() (PostgreSQL): split an email

• Use when one side stores [email protected] and you need name.

ON u.UserName = split_part(a.Email, '@', 1) 

2. substring() (many engines): join based on a fixed-position pattern

• Use when the matching value is embedded at a known position.

ON substring(b.CustomerCode from 1 for 5) = a.CustomerPrefix 

3. lower() / upper() (most engines): case-insensitive joins

• Use when the case differs between systems (e.g, John.Smith vs john.smith).

ON lower(a.UserName) = lower(b.UserName) 

4. trim() (most engines): remove leading/trailing spaces

• Use when one table stores padded values ('ABC ').

ON trim(a.RegionCode) = trim(b.RegionCode) 

5. concat() (varies by engine): join on a constructed key

• Use when one table has split fields and the other has a combined key.

ON concat(a.CountryCode, '-', a.StateCode) = b.RegionKey 

Tip: If you need multiple transformations, apply them consistently on both sides (for example, lower(trim(…))).

Expected Result

• After you modify the ON condition to transform values into a comparable format:
• Rows match correctly even when the stored formats differ (username vs full email, padded vs trimmed, mixed case, etc.).
• The data preview shows joined columns populated as expected (for example, Users.UserName correctly aligns with AuditLogins.Email).

Limitations and Considerations

• Database-specific function support: The SQL you write must be valid for the connected database engine (PostgreSQL/MySQL/SQL Server/Oracle/etc.).
• Performance impact: Functions in JOIN conditions can prevent index usage and may slow down queries on large tables. Test with realistic volumes and consider database-side options (computed columns, indexed views/materialized views) if performance is critical.
• Function compatibility across environments: If you move the data source from one database to another, you may need to rewrite function syntax.
• Testing recommendation: After every change:
  • Execute to validate SQL syntax.
  • Update to confirm row counts and correctness.
  • Validate edge cases (NULL emails, missing @, trailing spaces, mixed casing).
• Avoid ambiguous columns: Always reference columns with table aliases to avoid naming conflicts when joining.

Troubleshooting

Invalid SQL syntax

• Symptoms: Execute fails with a syntax error.
• Fix:
  • Confirm you’re using the correct function name and syntax for your database.
  • Ensure quotes, parentheses, and commas are correct.
  • Fully qualify columns with aliases (for example, u.UserName).

Function not supported

• Symptoms: Error like “function does not exist” or “invalid function”.
• Fix:
  • Replace the function with an equivalent for your engine (examples: split_part vs SUBSTRING_INDEX vs CHARINDEX/LEFT).
  • Validate directly in your database query tool (SSMS/pgAdmin/MySQL Workbench) using the same connection type.

Join returns no records (or fewer than expected)

Symptoms: Preview shows empty/missing joined columns.
Fix:

• Check the transformation output by temporarily selecting the transformed value:

SELECT a.Email, split_part(a.Email,'@',1) AS extracted_user
FROM AuditLogins a; 

• Normalize case using lower()/upper().
• Remove spaces using trim().
• Check for invalid data (NULLs, missing delimiter like @).

Data type mismatch

• Symptoms: Errors about comparing different data types.
• Fix:
  • Cast explicitly (engine-specific), for example CAST(...) / CONVERT(...).
  • Ensure both sides produce the same type (string-to-string, integer-to-integer).

Related Articles

• How to Join Tables Using Expression Columns in Bold BI
• How to Use Code View Mode Effectively in Bold BI
• Microsoft SQL Server – Switch to Code View and Execute/Update Queries