Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS Expression Help...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

224 Posts

Posted - 03/19/2015 :  10:53:42  Show Profile  Reply with Quote
Hi,

Need help. Here is my sample data

ID,Fname,CellPhone,OtherPhone,OID
1,Smith,2039131941,Null,1452
2,James,(203)913-1941,2039131942,6458
3,Chris,Null,(203)913-1943,1111


Here is my requirement.

If Length of CellPhone > 10 or < 10 or Null use OtherPhone
If Lenght of OtherPhone > 10 or < 10 or Null use OID


So far I have this expression in derived column

ContactNumber = (Len(CellPhone) > 10 || Len(CellPhone) < 10 || isnull(CellPhone) ? OtherPhone : CellPhone : (Len(OtherPhone) > 10 || Len(OtherPhone) < 10 || isnull(OtherPhone) ? OID : OtherPhone ))

I am not getting right result what I want, at the end I want below result (as an example)

ID,Fname,ContactInfo
1,Smith,2039131941
2,James,2039131942
3,Chris,1111

Please advise.

Thank You.

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/19/2015 :  11:11:50  Show Profile  Reply with Quote
That is not a valid expression! How is it working for you? In particular, there are two colons in the expression making it look like a three-way conditional expression, but there is no such thing.

e.g. this section is not valid:


... ? OtherPhone : CellPhone : ...


and will not compile. Please post the EXACT expression from the DC component.
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 03/19/2015 :  11:54:07  Show Profile  Reply with Quote
Sorry, here is the exp...

Len(CellPhone) > 10 || Len(CellPhone) < 10 || isnull(CellPhone) ? OtherPhone : (Len(OtherPhone) > 10 || Len(OtherPhone) < 10 || isnull(OtherPhone) ? OID : OtherPhone
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/19/2015 :  12:03:57  Show Profile  Reply with Quote
OK -- I simulated this. using your DC expression and data from your first line, for your first row I got:

ID,Fname,ContactInfo
1 ,Smith,1452

What do you actually wan to see?
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 03/19/2015 :  12:06:02  Show Profile  Reply with Quote
I want to see

ID,Fname,ContactInfo
1,Smith,2039131941
2,James,2039131942
3,Chris,1111

Thank You for your help!
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/19/2015 :  12:18:02  Show Profile  Reply with Quote
OK -- so the expression says:

if the cell phone number is null or not exactly 10 digits, then if the other phone is null or not exactly 10 digits, choose the OID.

In the first row, the cell phone number IS exactly 10 digits and the other phone IS null, so the OID is selected.

In other words, your expression does exactly what you said in words:

quote:

If Length of CellPhone > 10 or < 10 or Null use OtherPhone
If Lenght of OtherPhone > 10 or < 10 or Null use OID



However, your desired output does NOT match the above conditions. I think you need to clarify your conditions.
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 03/19/2015 :  12:25:16  Show Profile  Reply with Quote
Here is my source data (as an example)

ID,Fname,CellPhone,OtherPhone,OID
1,Smith,2039131941,Null,1452
2,James,(203)913-1941,2039131942,6458
3,Chris,Null,(203)913-1943,1111

I want, If Length of CellPhone > 10 or < 10 or Null Use OtherPhone, ( In first row Len of CellPhone is not > 10 or < 10 or Null, so it should Pick 2039131941)
Second Row, CellPhone is Null and len of OtherPhone is not > 10 or < 10 or Null, so it should pick 2039131942
Third Row, CellPhone is Null and Len of Other Phone is > 10 , so it should Pick 1111

Please guide me, if I am missing something. My expression is not 100% right that's why I am getting wrong data. I need help to fix my expression.

Thank You.

Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/19/2015 :  13:43:45  Show Profile  Reply with Quote
But your expression does not return CellPhone in any of the branches:

Len(CellPhone) > 10 || Len(CellPhone) < 10 || isnull(CellPhone) ? OtherPhone : (Len(OtherPhone) > 10 || Len(OtherPhone) < 10 || isnull(OtherPhone) ? OID : OtherPhone

so since the first row has a cellphone that is 10 digits, it takes the "else" branch:

(Len(OtherPhone) > 10 || Len(OtherPhone) < 10 || isnull(OtherPhone) ? OID : OtherPhone

Here, OtherPhone is null so it takes OID.

How about just:

LEN(CellPhone) == 10 ? CellPhone : LEN(OtherPhone) == 10 ? OtherPhone : OID
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 03/19/2015 :  13:58:09  Show Profile  Reply with Quote
I test your exp

LEN(CellPhone) == 10 ? CellPhone : LEN(OtherPhone) == 10 ? OtherPhone : OID

First and second rows looks good, third row I am getting Null.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/19/2015 :  14:16:15  Show Profile  Reply with Quote
LEN(CellPhone) == 10 && !ISNULL(CellPhone) ? CellPhone : LEN(OtherPhone) == 10 && !ISNULL(OtherPhone) ? OtherPhone : OID
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 03/19/2015 :  14:31:30  Show Profile  Reply with Quote
Looks great. Thank You for your Help!!

This question answered by gbritton last reply.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000