将 SQL 中的 CASE 表达式转换为 SSIS 中的派生列 [英] Convert CASE expression in SQL to derived column in SSIS

问题描述

CASE 
        WHEN CHARINDEX('%', '{FixedARMRateReductionLimit}') > 0 THEN
            CAST(SUBSTRING('{FixedARMRateReductionLimit}', 0, 
               CHARINDEX('%', '{FixedARMRateReductionLimit}')) as decimal)/100
        WHEN '{FixedARMRateReductionLimit}' = 'Weekly PMMS Rate' THEN
            PARAM_VAL_TXT
        ELSE
            .02
    END

推荐答案

以下故事的寓意是，仅仅因为您可以在 SSIS 中做一些事情，这并不总是一个好主意.

The moral of the following story is that just because you can do something in SSIS, it's not always a good idea.

举个例子，这个查询.使用现有的 sql 逻辑生成最终值比使用派生列或 SSIS 中的脚本任务要高效得多(更不用说浪费管道内存、CPU 等)

Case in point, this query. It would be far more efficient to use the existing sql logic to generate the final value than using derived columns or a script task in SSIS (not to mention the waste of pipeline memory, CPU, etc)

我使用以下作为源查询.

I used the following as a source query.

SELECT '50%' AS FixedARMRateReductionLimit, .1 AS PARAM_VAL_TXT
UNION ALL  SELECT 'Weekly PMMS Rate' AS FixedARMRateReductionLimit, .3 AS PARAM_VAL_TXT
UNION ALL  SELECT 'Frack',  .5

查找百分比位置

确定列中是否存在百分比符号.这将创建一个名为 PercentPosition

FINDSTRING(FixedARMRateReductionLimit, "%",1)

检查费率文本

如第一个表达式所示，做一个简单的比较就足够了，但我遇到了问题.我认为这是一个 字符串转换/比较 问题(请参阅第一个注释).我没有使用布尔值，而是使用 findstring 来生成序数位置.

Check for rate text

It should be sufficient to do a simple comparison as the first expression shows but I was having issues with it. I assume it's a string conversion/comparison issue (see first Note). Rather than diddle with getting a boolean value, I used findstring to generate the ordinal position.

FixedARMRateReductionLimit == "'Weekly PMMS Rate"
FINDSTRING(FixedARMRateReductionLimit,"Weekly PMMS Rate",1)

导出输出

享受三元运算符的双重用途.

(RateTextPosition > 0) ? (PARAM_VAL_TXT) : (PercentPosition == 0) ? .2 : ((DT_NUMERIC, 18,2) SUBSTRING(FixedARMRateReductionLimit,1,PercentPosition - 1))/100

您可以在脚本任务中简化其中的一些内容，但我只会在源代码中执行逻辑.

You could have simplified some of this in a script task but I'd just do the logic in the source.

这篇关于将 SQL 中的 CASE 表达式转换为 SSIS 中的派生列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！