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

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS Expression Help...

Author  Topic 

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-19 : 10:53:42
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 11:11:50
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 - 2015-03-19 : 11:54:07
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 12:03:57
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 - 2015-03-19 : 12:06:02
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 12:18:02
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 - 2015-03-19 : 12:25:16
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 13:43:45
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 - 2015-03-19 : 13:58:09
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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 14:16:15
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 - 2015-03-19 : 14:31:30
Looks great. Thank You for your Help!!

This question answered by gbritton last reply.
Go to Top of Page
   

- Advertisement -