Using a SQLServer Table Value Function in Pyramid
Hello,
Has any one tried using a SQLServer table valued function in a mode ?
For e.g. I have a TVF that tables date as an input
select * from schema.table(@date)
Where date is a literal that can be passed, so evaluates
select * from schema.table('2018-01-01')
Thanks
Mad
4 replies
-
Hi Mad,
Yes, you can. You'll need to add a Query Block connected to your SQLServer source, then create your Select statement using your Table Value Function like this:
In this example I've created a Table Value Function called getProductsInCategory that takes the product category as the parameter and returns the Product, StandardCost and ModelName from the Product table for the Product Category supplied.
select * from dbo.getProductsInCategory('Bikes').
You can see the output in the Preview pane.
You can also use Table Value Functions in Direct Query mode by adding a query like the one above to create the Table in Model.
You can even use Model variables to pass the literal to the function.
Bear in mind however, that you cannot vary the value of the parameter you pass dynamically at runtime for user queries in Discover.
Hope that helps.
Ian
-
Thanks so much, Ian
I was able to do as you indicated and was able to set a value for a parameter in the model and pass it as well and materialize it
How can I do this in DQ mode ?
Reason I ask, as soon as I add a query block and if DQ is checked , it does not let me connect the SQLServer node to the query block
As you indicated, there is no way I can have the parameter dynamically pass from a discover, that's good to know
Thanks again
Mad
-
In your DQ model, in the Tables view, add a Query to create a new table and call the TVF from there in the same way you did in the Select block in the data flow.
However, it is not possible to pass variables to the function here, only literals.
Hope that helps.
Ian
-
Ahh I see, yes, that I was able to do but was secretly hoping that I could use a variable there
Thanks Ian
Mad