FIXED How to create a userid using an if statement

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

Transact-SQL (2008)

FIXED How to create a userid using an if statement

Author

Topic

PHK
Starting Member

9 Posts

Posted - 2009-07-30 : 15:25:55

Hello,

I am looking to create userid's by picking out the first 2 digits out of a users assigned usernumber and combinging this with the last name, first names first initial and the middle initial. The problem is, is that the middlename has numerous nulls and I the more I work on it the uglier it is looking. Here is the ugliness that i have now, and that is before I make sure that each userid is unique where it assigns a number to the end of the userid if it is not unique:

If i.middlename IS NOT Null
select Left(p.usernumber,2) + i.lastname + Left(i.firstname,1) + Left(i.middlename,1) As userid
from dbo.person p,
dbo.id i
Else select Left(p.usernumber,2) + i.lastname + Left(i.firstname,1) As userid
from dbo.person p,
dbo.[identity] i
where p.personid = i.personid;

Sorry that this looks so terrible and thank you for your assistance in advance.

PHK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-30 : 15:45:36

You can get rid of teh IF statement and make use of the COALESE function:

select Left(p.usernumber,2) + i.lastname + COALESCE(Left(i.firstname,1), '') + Left(i.middlename,1) As userid
from dbo.person p,
dbo.id i

Bah, I meant to COALESCE the middle name, but you get the idea.. :)

PHK
Starting Member

9 Posts

Posted - 2009-07-30 : 16:16:23

Thank you. I have forgot all about the Coalesce, been a few years since I have worked in the SQL Server environment and things are coming back slower than I expected.
I am now getting an error though that states:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name ' '.

Thanks again in advance.

PHK

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-30 : 16:47:59

Can you please show us the query that you tried?

PHK
Starting Member

9 Posts

Posted - 2009-07-30 : 16:59:37

Sorry here it is:

Select Left(p.usernumber,2) + i.lastname + Left(i.firstname,1) + COALESCE(Left(i.middlename,1), " ") As userid
from dbo.person p,
dbo.id i
where p.personid = i.personid;

I have also tried CAST and CASE and pretty much what I could think of in some variance. The biggest problem is all the nulls in the middle name. When I just run this:
select Left(p.usernumber,2) + i.lastname + Left(i.firstname,1) + Left(i.middlename,1) AS userid
from dbo.person p,
dbo.id i
where p.personid = i.personid;

it returns Null for those that have Null middle name.

PHK
Starting Member

9 Posts

Posted - 2009-07-30 : 17:01:43

UGH! Ok I was inserting "" instead of ''.

Sorry to take up more of your time.

Thanks to all that helped out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources