none
Reading a scalar value from a file and using it as a parameter to a TVF

    Question

  • Hello,

    I want to read a configuration date from a file and use it in a TVF. The configuration being read is a date and it is stored in a *.tsv file. The configuration will have just one row and one column.

    I'm trying to use a script to one as below but it fails with the message:

    "Rowset variable '@LastProcessedDate' is not a scalar variable."

    Reading through the blog it appears that there is no way to implicitly convert single rowset variable to a scalar value.

    Is there any method to accomplish this or any other approach? Appreciate your help.

    //Script

    DECLARE @output = string.Format("datawarehouse/datamart/TestDimension.tsv");

    @LastProcessedDate =
        SELECT MAX(LastProcessedDate) AS LastProcessDate
        FROM tbl_TestDimensionConfig() AS A;
                  
                      
    @new = SELECT  Column1,Column2
                    FROM tbl_Dimension
                (
                  DateTime.Parse(LastProcessedDate)
                ) AS P
                CROSS JOIN @LastProcessedDate AS p1;

                
    OUTPUT @new
    TO @output
    USING Outputters.Tsv(quoting : false, dateTimeFormat : null);

    Tuesday, April 11, 2017 12:40 AM

All replies

  • Hi there,

    Could you use a procedure?  And instead of having a configuration file (or in addition to) create a USQL script with the parameter as part of the data?

    This or a simplified version of this could be created by a previous USQL script that exports the following based on your input file parameter:

    EXEC mydb.myproc("2012-11-12");

    Procedure:

    CREATE DATABASE IF NOT EXISTS mydb;

    CREATE PROC mydb.myproc (@LastProcessedDate String)

    AS

    BEGIN;

    @new = SELECT  Column1,Column2
                    FROM tbl_Dimension
                (
                  DateTime.Parse(@LastProcessedDate)
                ) AS P ;

    OUTPUT @new
    TO @output
    USING Outputters.Tsv(quoting : false, dateTimeFormat : null);

    END;

    Wrapper script can be generated using this method.

    http://stackoverflow.com/questions/42636855/u-sql-output-in-azure-data-lake

    cheers,

    Andrew


    Andrew Sears

    Wednesday, April 12, 2017 1:23 AM