Choose two values (out of four possible)

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 2005 Forums

Transact-SQL (2005)

Choose two values (out of four possible)

Author

Topic

ajmufic
Starting Member

17 Posts

Posted - 2008-05-06 : 08:35:43

(Hard to put a good subject on this one...)

I have a database containing a lot of users and these users can have four different kind of telephone numbers connected to them: "Direct phone", "Switchboard", "Cell phone", "Home phone". The phone numbers are stored in a separate table. Some users have 0 phone numbers, some have 1, some have 3 etc.

Now I have to transfer the data to another database with a strict table structure and here the table that contains the user also should contain the users phone number and an alternative phone number, if the user currently has more than one phone number connected.

This means that if for instance we have three or more phone numbers connected to one user, we can maximum transfer two of them. This is not a big issue though...

We have ranked the importance of the phone numbers in the order as I presented them above.
What I do in my T-SQL query is to do a ISNULL() and see if the user has "Direct phone" connected, if not I check for the next type and so on.

Now to my problem! Can anyone give me a suggestion of how to write the code for the extraction of the Alternative phone? What I need to do is to check if there is a "Direct phone" connected to the user, if so I should NOT chose that but the next phone number that I find.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-06 : 08:52:29

select Phone = coalesce(Main_phone,AlternatePhone) from table

You can also use case statements.

or you can nest your coalesce statements like this

select Phone = coalesce(Main_phone,coalesce(AlternatePhone,coalesce(phone2,phone3))) from table --- this would put one of four values into phone depending on whether the values are null or not and what the priority is.

If you need more help, provide sample data and expected results.

An infinite universe is the ultimate cartesian product.

ajmufic
Starting Member

17 Posts

Posted - 2008-05-06 : 08:57:22

Hi cat_jesus and thanks for your reply!

coalesce is perfect for my main phone number but for my alternate phone I need the SECOND nonnull expression amongs the four phone arguments.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 09:28:34

COALESCE is most useful if the phone numbers are not normalized,
UserID, Telephone1, Telephone2, Telephon3, Telephone4.

If your telephone table is normalized

UserID, TelephoneType, TelephoneNumber

You must need another approach.

E 12°55'05.25"
N 56°04'39.16"

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-06 : 10:09:09

[code]

declare @PrimaryPhone as varchar(10)
declare @secondaryPhone as varchar(10)

declare @phone1 as varchar(10)
declare @phone2 as varchar(10)
declare @phone3 as varchar(10)
declare @phone4 as varchar(10)

set @phone1 = null
set @phone2 = '1234567'
set @phone3 = null
set @phone4 = '789789789'

select @PrimaryPhone = coalesce(@phone1,coalesce(@phone2,coalesce(@phone3,@phone4)))
select @primaryPhone

select @SecondaryPhone = Case when @phone1 is not null and @phone2 is not null then @phone2
when @phone1 is null and @phone2 is not null then coalesce(@phone3,@phone4)
else '33333' end
select @SecondaryPhone
[/code]

Like this?

An infinite universe is the ultimate cartesian product.

ajmufic
Starting Member

17 Posts

Posted - 2008-05-07 : 07:22:55

Thanks for your answers guys, the issue had now been solved!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 07:42:26

cat_jesus, there is no need to nest COALESCE function!

select @PrimaryPhone = coalesce(@phone1, @phone2, @phone3, @phone4, 'no phone number available')

E 12°55'05.25"
N 56°04'39.16"

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-07 : 10:08:36

Thanks Peso. It's a bad habit(from using isnull).

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources