Post reply

SSIS -variable scope in derived column

  • September 24, 2011 at 3:57 am

    #248367

    I want to check if expressions in a derived column run on a sequential manner or in parallel.I have a variable new_cost.I want to check if the new_cost calculated in derived column new_cost will be used below for calculating daily_change_pnl and new_trade_pnl or will daily_change_pnl and new_trade_pnl use the value calculated before calculating new_cost.attachment attached herewith

  • September 24, 2011 at 1:27 pm

    #1386129

    duggal.priyanka06 (9/24/2011)

    I want to check if expressions in a derived column run on a sequential manner or in parallel.I have a variable new_cost.I want to check if the new_cost calculated in derived column new_cost will be used below for calculating daily_change_pnl and new_trade_pnl or will daily_change_pnl and new_trade_pnl use the value calculated before calculating new_cost.attachment attached herewith

    These are not done in an order of precedence. You can check this with some dummy data like:

    SELECT 1 AS Id, 'Joe' AS Name, 3.31 AS NewCost, 'Y' AS DailyChangePnl, 'Y' AS NewTradePnl

    UNION ALL

    SELECT 2 AS Id, 'Fred' AS Name, 15.56 AS NewCost, NULL AS DailyChangePnl, 'N' AS NewTradePnl

    UNION ALL

    SELECT 3 AS Id, 'Dan' AS Name, NULL AS NewCost, 'N' AS DailyChangePnl, NULL AS NewTradePnl

    UNION ALL

    SELECT 4 AS Id, 'Andy' AS Name, -2.99 AS NewCost, NULL AS DailyChangePnl, NULL AS NewTradePnl

    UNION ALL

    SELECT 5 AS Id, 'Tom' AS Name, 9.95 AS NewCost, 'N' AS DailyChangePnl, 'N' AS NewTradePnl

    Then put a couple lines in a derived column task like:

    Replace 'New Cost' | ISNULL(NewCost) ? 0 : NewCost + 5

    Replace 'Name' | NewCost < 0 ? "Less than zero" : "Zero & up"

    See the attached graphic

    HTH,

    Rob

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply