首页 文章

u-sql脚本无法从数据集中获取标量值

提问于
浏览 次
-2

在u-sql脚本中，我必须从文件中提取变量到数据集，然后使用它来形成输出文件的名称 . 如何从数据集中获取变量？

详情 .

我有2个输入文件：带有一组字段和字典文件的csv文件 . 第一个文件的文件名如**** ClintCode ***** . csv . 第二个文件字典有2个带映射的字段：ClientCode - ClintCode2 . 我的任务是从文件名中提取ClientCode值，从字典中获取ClientCode2，将其作为字段插入到输出文件（已实现），此外，将输出文件的名称形成为**** ClientCode2 **** . CSV .

字典csv文件的内容如下：

OldCode NewCode

6HAA阿尔法

CCVV Beta

CVXX伽马

？戴维斯

问题是如何将ClientCode2转换为标量变量来为输出文件编写表达式？

DECLARE @inputFile string = "D:/DFS_SSC_Automation/Tasks/FundInfo/ESP_FAD_GL_6HAA_20170930.txt";  // '6HAA' is ClientCode here that mapped to other code in ClientCode_KVP.csv

DECLARE @outputFile string = "D:/DFS_SSC_Automation/Tasks/FundInfo/ClientCode_sftp_" +   // 'ClientCode' should be replaced with ClientCode from mapping in ClientCode_KVP.csv
                            DateTime.Now.ToString("yyyymmdd") + "_" +
                            DateTime.Now.ToString("HHmmss") + ".csv";

DECLARE @dictionaryFile string = "D:/DFS_SSC_Automation/ClientCode_KVP.csv";


@dict =
    EXTRACT [OldCode] string,
            [NewCode] string
    FROM @dictionaryFile
    USING Extractors.Text(skipFirstNRows : 1, delimiter : ',');

@theCode =
    SELECT Path.GetFileNameWithoutExtension(@inputFile).IndexOf([OldCode]) >= 0 ? 1 : 3 AS [CodeExists],
           [NewCode]
    FROM @dict
    UNION
    SELECT *
    FROM(
        VALUES
        (
            2,
            ""
        )) AS t([CodeExists],[NewCode]);

@code =
    SELECT [NewCode]
    FROM @theCode
ORDER BY [CodeExists]
FETCH 1 ROWS;


@GLdata = 
    EXTRACT [ASAT] string,
    [ASOF] string,
    [BASIS_INDICATOR] string,
    [CALENDAR_DATE] string,
    [CR_EOP_AMOUNT] string,
    [DR_EOP_AMOUNT] string,
    [FUND_ID] string,
    [GL_ACCT_TYPE_IND] string,
    [TRANS_CLIENT_FUND_NUM] string
FROM @inputFile
USING Extractors.Text(delimiter : '|', skipFirstNRows : 1);

// Prepare output dataset

@FundInfoGL =
    SELECT "" AS [AccountPeriodEnd],
           "" AS [ClientCode],
           [FUND_ID] AS [FundCode],
           SUM(GL_ACCT_TYPE_IND == "A"? System.Convert.ToDecimal(DR_EOP_AMOUNT) : 0) AS [NetValueOtherAssets],
           SUM(GL_ACCT_TYPE_IND == "L"? System.Convert.ToDecimal(CR_EOP_AMOUNT) : 0) AS [NetValueOtherLiabilities],
           0.0000 AS [NetAssetsOfSeries]
    FROM @GLdata
    GROUP BY FUND_ID;


// NetAssetsOfSeries calculation

@FundInfoGLOut =
    SELECT [AccountPeriodEnd],
           [NewCode] AS [ClientCode],
           [FundCode],
           Convert.ToString([NetValueOtherAssets]) AS [NetValueOtherAssets],
           Convert.ToString([NetValueOtherLiabilities]) AS [NetValueOtherLiabilities],
           Convert.ToString([NetValueOtherAssets] - [NetValueOtherLiabilities]) AS [NetAssetsOfSeries]
    FROM @FundInfoGL
    CROSS JOIN @code;


// Output

OUTPUT @FundInfoGLOut
TO @outputFile
USING Outputters.Text(outputHeader : true, delimiter : '|', quoting : false);
u-sql

2 回答

  • 0

    你不能 . 请参阅Convert Rowset variables to scalar value .

    您仍然可以以不同的方式实现您的最终目标 . 请考虑使用清晰简洁的语言，小数据集，预期输出以及重新生成所需的极少量代码来重写您的帖子 - 删除创建测试用例不必要的所有细节和细微差别 .

    回复于
  • 0

    正如David所指出的：您无法将查询结果分配给标量变量 .

    但是，我们现在在私有预览中具有动态分区输出功能，这使您能够根据列值生成文件名 . 如果你想尝试一下，请联系我 .

    回复于

相关问题