Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS expression need help Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2013 3:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 4:28 AM
Points: 8, Visits: 71
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!!
Post #1513397
Posted Wednesday, November 13, 2013 7:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:35 AM
Points: 3,054, Visits: 780
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, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #1513867
Posted Thursday, November 14, 2013 2:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:08 PM
Points: 74, Visits: 167
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)
Post #1514493
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse