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.
Author 
Topic 
tooba
Posting Yak Master
224 Posts 
Posted  20150319 : 10:53:42

Hi,
Need help. Here is my sample data
ID,Fname,CellPhone,OtherPhone,OID 1,Smith,2039131941,Null,1452 2,James,(203)9131941,2039131942,6458 3,Chris,Null,(203)9131943,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  20150319 : 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 threeway 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. 


tooba
Posting Yak Master
224 Posts 
Posted  20150319 : 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 


gbritton
Master Smack Fu Yak Hacker
2780 Posts 
Posted  20150319 : 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? 


tooba
Posting Yak Master
224 Posts 
Posted  20150319 : 12:06:02

I want to see
ID,Fname,ContactInfo 1,Smith,2039131941 2,James,2039131942 3,Chris,1111
Thank You for your help! 


gbritton
Master Smack Fu Yak Hacker
2780 Posts 
Posted  20150319 : 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. 


tooba
Posting Yak Master
224 Posts 
Posted  20150319 : 12:25:16

Here is my source data (as an example)
ID,Fname,CellPhone,OtherPhone,OID 1,Smith,2039131941,Null,1452 2,James,(203)9131941,2039131942,6458 3,Chris,Null,(203)9131943,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.



gbritton
Master Smack Fu Yak Hacker
2780 Posts 
Posted  20150319 : 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 


tooba
Posting Yak Master
224 Posts 
Posted  20150319 : 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. 


gbritton
Master Smack Fu Yak Hacker
2780 Posts 
Posted  20150319 : 14:16:15

LEN(CellPhone) == 10 && !ISNULL(CellPhone) ? CellPhone : LEN(OtherPhone) == 10 && !ISNULL(OtherPhone) ? OtherPhone : OID 


tooba
Posting Yak Master
224 Posts 
Posted  20150319 : 14:31:30

Looks great. Thank You for your Help!!
This question answered by gbritton last reply. 






