November 12, 2013 at 3:53 am
Not sure if this is good area to post this but could not find specific SSIS place for it. I have below transformation rule which I am trying to put it in derived column. Having issues with how I can set two separate case with else. please help.
The numbers are CASE_STATUS
1=A1021002
2=A1021002
3=A1021002
4=A1021002
5=A1021002
6=A1021002
7=A1021003
8=A1021003
10=(CASE WHEN C.CASE_STATUS = 10 THEN
CASE WHEN E.REJECT_REASON IS NULL THEN 'A1021006'
WHEN E.REJECT_REASON = '71' THEN 'A1021007'
ELSE 'A1021008'
END
END)
11=A1021002
12=A1021011
13=A1021002
14=A1021002
15=A1021002
16=A1021002
17=A1021002
18=A1021002
Tried this but got an error
((CASE_STATUS == 10 && ISNULL( REJECT_REASON)) ? "A1021006" : ((CASE_STATUS == 10 && REJECT_REASON == "71") ? "A1021007" : "A1021008")) : (CASE_STATUS == 1 || CASE_STATUS == 2 || CASE_STATUS == 3 || CASE_STATUS == 4 || CASE_STATUS == 5 || CASE_STATUS == 6 || CASE_STATUS == 11 || CASE_STATUS == 13 || CASE_STATUS == 14 || CASE_STATUS == 15 || CASE_STATUS == 16 || CASE_STATUS == 17 || CASE_STATUS == 18) ? "A1021002" : (CASE_STATUS == 7 || CASE_STATUS == 8) ? "A1021003" : CASE_STATUS == 12 ? "A1021011" : "")
Thanks in advance!!
November 13, 2013 at 7:04 am
You probably need to stage the data in a table, then use T-SQL as Source Component and write CASE statement in T-SQL.
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
November 14, 2013 at 2:51 pm
It would be useful to know the exact error condition being generated. However I would work towards simplifying the expression by
a) including an additional OLE SRCE dataset which included the basic transformations, ie
SELECT 1 AS CASE_STATUS
,'A1021002' AS RESULT
UNION ALL
SELECT 2 AS CASE_STATUS
,'A1021002' AS RESULT
etc etc
UNION ALL
SELECT 10 AS CASE_STATUS
,null AS RESULT
etc etc
b) doing a merge join wth the primary data source to return the results for everything other than case_status 10
c) having the derived column expression to include the additonal logic for Case_Status 10 and the associated Reject_Reason (s)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply