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?
