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
provider_id | provider_npi | full_name | first_name | last_name | gender |
---|---|---|---|---|---|
1 | 456 | John Doe | John | Doe | m |
2 | 789 | Jane Doe | Jane | Doe | f |
3 | 756 | Fred Jones | Fred | Jones | m |
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.
link_id | provider_id | link_category | link_type | link_name | link_value |
---|---|---|---|---|---|
1 | 1 | Image | URL | Provider Photo Web Link 2 | a |
2 | 1 | Web Property | URL | Leap Profile Image | b |
3 | 1 | Web Property | Text | About Me - Spanish | c |
4 | 1 | Web Property | URL | Web Page Link - Spanish | d |
5 | 2 | Image | URL | Provider Photo Web Link 2 | 123 |
6 | 2 | Web Property | Text | About Me - Cantonese | xyz |
7 | 3 | Image | URL | Provider Photo Web Link 2 | 3 |
8 | 3 | Image | URL | Provider Photo Web Link 2 | 2 |
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.
Attribute Name: Set a descriptive name.
SQL Cust attribute - 'Link Name = Provider Photo Web Link 2' and Provider ID
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.
Target Table: Select the target table which is the one side of the one-to-many relationship.
Provider
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)
Theselect
is implied, but we call the SQL function as if it were a field that was being selected in aselect
and pass it the appropriate attributes.Function:
dbo.returnLinkValue_V1_Param_1_Link_Name_Param_2_ProviderID()
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'
.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
.
Data Type/Data Length:
String
of length50
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.
Identify the previously created attribute in the file system, right-click it, and select copy.
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.Then you can right-click the new attribute in the file system and select Configure attribute.
Update the description and the SQL Expression to pass
'About Me - Spanish'
as a link name.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
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.
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:
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.
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
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.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