June 3, 2019 at 9:49 pm
How to substract values from column a of table a to Column b of table a( yes its a same table) using ssis??
Can we use execute sql task and write t-sql code??
Or which transformation to use??
June 3, 2019 at 9:56 pm
Is this as part of a data flow? If yes, add a derived column to your data flow and put the calculation in there.
ExecuteSQL tasks do exactly what they say – so if you can do what you need to do in T-SQL, you can do it using an ExecuteSQL task. Based on what you say, this sounds like the better solution: a simple UPDATE query.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 4, 2019 at 1:45 am
Thanks. It's inside Data Flow task and I have hard time compiling this IF condition.
IF TOTAL_BILL_AMT - TOTAL_BILL_AMT_PRD = 0 then 0 Else 1.
Here is what I wrote but error, [TOTAL_BILL_AMT] - [TOTAL_BILL_AMT_PRD] == "0" ? "0" : "1"
Also I tried,
(DT_NUMERIC,10,2) [TOTAL_BILL_AMT] - (DT_NUMERIC,10,2)) [TOTAL_BILL_AMT_PRD] == "0" ? "0" : "1"
Error at Data Flow Task [Derived Column [170]]: Attempt to parse the expression "(DT_NUMERIC,10,2) [TOTAL_BILL_AMT] - (DT_NUMERIC,10,2)) [TOTAL_BILL_AMT_PRD] == "0" ? "0" : "1"" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
June 4, 2019 at 2:26 am
What are the data types of TOTAL_BILL_AMT and TOTAL_BILL_AMT_PRD within the data flow?
Also, try removing the quotes throughout the expression – if the 0, 0 and 1 are supposed to be integers, they should not require them.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 4, 2019 at 2:30 am
Thanks. Datatypes for both are decimal(10,2)
if the difference =0 then insert 1 (bit datatype) or 0
June 4, 2019 at 2:39 am
Thanks it worked finally. Really appreciate it.
(DT_NUMERIC,10,2)TOTAL_BILL_AMT - (DT_NUMERIC,10,2)TOTAL_BILL_AMT_PRD == 0 ? 0 : 1
June 4, 2019 at 3:46 am
Please also try this ... you shouldn't need those casts:
TOTAL_BILL_AMT - TOTAL_BILL_AMT_PRD == 0 ? 0 : 1
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 4, 2019 at 4:12 pm
IF TOTAL_BILL_AMT_PROD = NULL THEN NULL ELSE (TOTAL_BILL_AMT - TOTAL_BILL_AMT_PRD = O then 0 Else 1 )
ISNULL(TOTAL_BILL_AMT_PRD) == ""? NULL(DT_STR, 4, 1252) : ((DT_NUMERIC,10,2)TOTAL_BILL_AMT_PRD - DT_NUMERIC,10,2)TOTAL_BILL_AMT == 0 ? 1 : 0) gives error.
June 4, 2019 at 4:26 pm
IF TOTAL_BILL_AMT_PROD = NULL THEN NULL ELSE (TOTAL_BILL_AMT - TOTAL_BILL_AMT_PRD = O then 0 Else 1 ) ISNULL(TOTAL_BILL_AMT_PRD) == ""? NULL(DT_STR, 4, 1252) : ((DT_NUMERIC,10,2)TOTAL_BILL_AMT_PRD - DT_NUMERIC,10,2)TOTAL_BILL_AMT == 0 ? 1 : 0) gives error.
Is this for our information, or is there a question lurking in there somewhere?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 5, 2019 at 5:09 pm
Try the following:
ISNULL(TOTAL_BILL_AMT_PRD) == True ? (DT_NUMERIC,1,0) NULL : ((TOTAL_BILL_AMT_PRD - TOTAL_BILL_AMT) == 0 ? 1 : 0)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply