One-to-many relationships and custom SQL functions

SQL expression attributes: RPI custom attributes

For information about SQL expression attributes, refer to https://docs.redpointglobal.com/rpi/sql-expression-attribute-properties.

SQL expression attributes allow you to expose a valid SQL expression as an attribute. There are various uses for these types of custom attributes; one application can be to help identify the single record/attribute that you are trying to export or use for personalization that is on the many side of a one-to-many (1:M) relationship.

This functionality is necessary because of how Redpoint Interaction (RPI) natively behaves when extracting and personalizing data from the many side of a 1:M relationship. This is explained in the parent page of this topic.

SQL expression example

Here is a simple illustration of how this approach would work.

Data related to example

Let’s say that we have a one-to-many (1:M) relationship between provider and links as this high-level entity relationship diagram (ERD) shows.

And we have a set of data in each of those tables to support this example. An illustration of the data in table format is listed below.

Provider table

Links table

The links table can have many records for a single provider_id and represents the many side of this example. There is an added column called link_id in this table that is not in the ERD above but was added to make all of the rows unique. This is because the last provider_id (3) has two link_names that are the same but have different values. The expectation with the links table is that using a combination of the fields will ensure each row will be unique, and a SQL function can use those same fields as parameters to provide back a unique value. Provider ID 3 is used to illustrate when that criteria is not enforced.

Create a SQL function

Now that we have established the ERD and a set of sample data, we can look at creating a SQL function for the purpose of returning a single value from the links table (many side) based on a set of parameters. We start by determining what value we are looking to get returned from the function and what parameters need to be passed to get the value we are looking to have returned. In this example, we are looking to get the link_value returned when the function is called. To start off, we are going to use two parameters to determine the value from the links table to return. The two parameters will be the provider_id and the link_name.

Example function

This example function was created using Microsoft SQL Server and is based off of the tables listed above. We start by constructing a SQL expression that will join the provider and the links table using the input variables to dynamically construct the join between the tables using the provider_links.link_name and the input variable link_name as the join criteria. In addition to that, the input variable provider_id is used in the where clause to compare the provider_links.provider_id. Based on the links table, this should only result in a single value being returned.

You must ensure that the function only returns a single result based on the parameters that are passed to it to generate consistent and expected results.

This example function is just used to illustrate the options you have to construct a query using the tables and the input variables without consideration for the most efficient way to construct this query. When using this type of feature, you should make sure to tune your query for the best performance to mitigate any issues when calling the function.

Example function SQL code

USE [AdventureWorksDW2022]
GO

/****** Object:  UserDefinedFunction [dbo].[returnLinkValue_V1_Param_1_Link_Name_Param_2_ProviderID]    Script Date: 8/21/2024 12:23:31 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[returnLinkValue_V1_Param_1_Link_Name_Param_2_ProviderID](@link_name varchar (1000), @input_provider_id int )
RETURNS  varchar (Max)
AS
-- Returns the link_value based on the input parameters of link_name and provider_id.
BEGIN
  DECLARE @ret varchar (Max)
	SELECT @ret =  link_value
	FROM [AdventureWorksDW2022].[dbo].[provider] provider
	join [dbo].[provider_links] provider_links on provider_links.[link_name] = @link_name
	Where
	provider_links.provider_id = @input_provider_id
  RETURN @ret;
END;
GO

Configure RPI to use SQL expression attribute

Now that we have the data in the tables and the SQL function, you are ready to configure the SQL expression attribute in RPI.

Create SQL expression attribute

Create a new attribute in RPI and choose the SQL Expression option.

Configure SQL expression attribute

Below is a screenshot of configuring a SQL expression attribute.

1. Attribute Name: Set a descriptive name.
   SQL Cust attribute - 'Link Name = Provider Photo Web Link 2' and Provider ID

2. Attribute Description: Optionally set a description.
   Calls a SQL function with a link name and a provider id and get a link value as a result.

3. Target Table: Select the target table which is the one side of the one-to-many relationship.
   Provider

4. SQL Expression: Define a SQL Expression that calls the function that we created in the previous steps.
   dbo.returnLinkValue_V1_Param_1_Link_Name_Param_2_ProviderID('Provider Photo Web Link 2', {alias}.provider_id)
   The select is implied, but we call the SQL function as if it were a field that was being selected in a select and pass it the appropriate attributes.

   1. Function: dbo.returnLinkValue_V1_Param_1_Link_Name_Param_2_ProviderID()

   2. Parameter 1: Text value that will be used to identify the link name in the links table. In this example the value is set to 'Provider Photo Web Link 2'.

   3. Parameter 2: Provider_ID, which is a field that is being passed to the function to identify the provider id to look up the value for, including the feature to reference an alias for the table ({alias}), {alias}.provider_id.

5. Data Type/Data Length:
   String of length 50

6. Validate: SQL Expression Attributes need to be validated successfully before they can be saved.

Configure additional SQL expression attributes

Now that we have a SQL expression attribute created, we can use that attribute to create additional ones based on the same SQL function.

1. Identify the previously created attribute in the file system, right-click it, and select copy.

2. Then enter the name of the new attribute you want to create, "SQL Cust attribute - Link Name = About Me - Spanish And Provider" in this example, and the location to save it.

3. Then you can right-click the new attribute in the file system and select Configure attribute.

4. Update the description and the SQL Expression to pass 'About Me - Spanish' as a link name.

5. Save the attribute, and you are ready to use it.

You can create additional attributes in RPI in this way if you know the link name to get the value from.

Illustrate the SQL expression attribute/SQL function behavior using an export template

Next, we will create an export template using the SQL expression attributes to illustrate their behavior.

Configure an export template

1. Give the export a name and configure the resolution to align with your configuration; in our case that is provider. Set the additional options as you see fit.

2. Add Attributes to the export.

Review export template behavior

Now that we have created an export template, we can use a selection rule against the provider resolution, and then use the data viewer tool with the export template to review the data.

Simple selection rule (This selection rule selects all records in the provider table):

Data viewer:

1. There are null values for two of the records for “About Me - Spanish” because those two provider records do not have a corresponding record in the links table with that name.

2. For Provider ID 3, the result for “Provider Photo Web Link 2” is 3. We know that we set up that record to have duplicate records. The value of the second key is a 2, but we got the value of 3 in the data viewer. The first value encountered is what is returned. So it is recommended that you prevent this when creating a function to ensure you only get one specific value. You can do various things with the SQL function to avoid this issue: additional parameters, or a modified selection statement, or by managing the data that is loaded to the links table so multiple records are not allowed.

Additional considerations

1. SQL Function
   In this example, a SQL function that takes two parameters is used with a SQL expression attribute in RPI to retrieve the link value from the links table. Two parameters were used in the example, but additional ones could be added to the function and used to support the selection of a specific record from the links table. The number of parameters required are based on the criteria required to identify a unique record from the table with multiple records, in this case the links table.

2. Performance Considerations
   There are various ways that this feature can be used, and the specific way that you intend to use this feature should be evaluated to ensure it meets your expectations for performance. Because the use cases and the expectations for performance will vary by customer, this requires testing your specific use cases with your SQL function at scale to be confident it will perform as you need it to. If it does not, you may need to consider some other options related to addressing the 1:M behavior.

Reference links

• Microsoft SQL Server Function Reference Documentation: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16

• PostgreSQL Function Reference Documentation: https://www.postgresql.org/docs/current/sql-createfunction.html