Question

Using Table Value Functions as a derived table with Paramters

  • 14 November 2017
  • 1 reply
  • 177 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