Question

Using Table Value Functions as a derived table with Paramters

  • 14 November 2017
  • 1 reply
  • 155 views

Userlevel 1

I’m currently testing using table valued functions as a derived table for a looker view. However, I’m curious as to how something is being applied within the LookML that’s returning different results.


The table valued function I’m testing with is very simple and looks as such:-


CREATE FUNCTION [User].[tbvGetMyUserID]
(@WhoAmI nvarchar(255))
RETURNS table
AS
RETURN (

SELECT UserId
FROM DIM.EmployeeUsers AS EU
WHERE EU.Email = @WhoAmI
);

GO

The LookML for the view looks something like this:-


view: my_user_id {
derived_table: {sql:
SELECT UserId
FROM [User].[tbvGetMyUserID] ({% parameter my_user_id.email %});;
}

parameter: email {
type: string
}

dimension: UserId {
sql: ${TABLE}.UserID;;
type: number

}
}

However, when I try and explore using this, if I try and use the user attribute for email, then it doesn’t work, but if I manually enter that as a string, then it does. The generated SQL for the first case looks thus:-


WITH my_user_id AS (SELECT UserId
FROM [User].[tbvGetMyUserID] ('{{ _user_attributes[''email''] }}'))
SELECT
TOP 500
my_user_id.UserID AS "my_user_id.userid_1"
FROM my_user_id

GROUP BY my_user_id.UserID
ORDER BY 1

If I hard code the string it looks thus:-


WITH my_user_id AS (SELECT UserId
FROM [User].[tbvGetMyUserID] ('me@whatever.com'))
SELECT
TOP 500
my_user_id.UserID AS "my_user_id.userid_1"
FROM my_user_id

GROUP BY my_user_id.UserID
ORDER BY 1

Is there anything in particular that needs to be done to make this work?


1 reply

Userlevel 2

Hello @Matthew.Darwin,


By filtering on a user attribute within a parameter of type string, the user attribute variable is being interpreted as a string instead of evaluating to its assigned value.


For instance, take my example below where I’m filtering on a parameter of type string with a user attribute. As you can see in the generated sql, the user attribute variable is being interpreted as a string.


 parameter: state_param {
type: string
}
dimension: is_ca {
type: yesno
sql: ${state} = {% parameter users.state_param %} ;;
}



Instead, what you could try is referencing the user attribute liquid variable itself in the the my_user_id derived table sql. Doing so in my example yields these results:


dimension: is_ca {
type: yesno
sql: ${state} = "{{ _user_attributes['state'] }}" ;;
}


I hope that helps!


Thanks,

Philip M.

Reply