query returning blank - SQL Server Forums

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

Transact-SQL (2000)

query returning blank

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-12-21 : 02:34:21

i'm doing the following:

select id,lastname+',' +firstname as cname

usually this returns the lastname,firstname as I want but if the lastname or firstname it returns blank rather then the lastname/firstname (whichever is not blank.)
Am i doing something wrong?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-21 : 02:49:19

ahhh.... there are probably null values here, handle the null values with coalesce or issnull

select id,COALESCE(lastname, '') + ',' + COALESCE(firstname, '') as cname

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-12-21 : 02:58:56

thnaks -- this is actually in access and not sql server - any idea for the correct syntax?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-21 : 03:06:35

No, sorry - don't know the access syntax, but you should have put this in the Access Forum [url]http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3[/url]

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-21 : 03:08:47

select id,IIF(Isnull(lastname)=1, '',lastname)) + ',' + IIF(Isnull(lastname)=1, '',firstname)) as cname from yourTable

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-12-21 : 03:21:53

thanks for your help but it is still returning blanks..

select id,IIF(Isnull(lastname)=1,'',lastname) + ',' + IIF(Isnull(lastname)=1, '',firstname) as cname

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-21 : 04:05:44

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-12-21 : 04:12:22

samele data woudl be

firstname lastname
john doe
smith
abe
joe shmoe

as you see some of the firstnames are missing and some of the lastnames
i wante it to return
doe,john
smith,
,abe
shmoe,joe

basically return what information i have

thiyait
Yak Posting Veteran

70 Posts

Posted - 2005-12-21 : 04:36:55

quote:
Originally posted by esthera

samele data woudl be

firstname lastname
john doe
smith
abe
joe shmoe

as you see some of the firstnames are missing and some of the lastnames
i wante it to return
doe,john
smith,
,abe
shmoe,joe

basically return what information i have

just try this ma
select id,IIF(Isnull(lastname)=1,'',lastname) & ',' & IIF(Isnull(lastname)=1, '',firstname) as cname

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-12-21 : 04:48:05

thanks that seems to work!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources