0

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

null
    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    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

    • Mad_Amruthur
    • 1 yr ago
    • Reported - view

    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

    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    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

    • Mad_Amruthur
    • 1 yr ago
    • Reported - view

    Ahh I see, yes, that I was able to do but was secretly hoping that I could use a variable there

    Thanks Ian

    Mad

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 4Replies
  • 34Views
  • 2 Following