merging two colums,of which one can be null

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)

merging two colums,of which one can be null

Author

Topic

Kirun
Yak Posting Veteran

60 Posts

Posted - 2009-12-23 : 06:28:35

in tableA there is column which can be Null . I want to merged that column to a table colums whihc cant be Null. when i am merging its giving me results like if the tableA has NULL Value ,its showing NUll . I want if the values of table A is null its still show the value of Tabele2

my query is
SELECT eJournal.eJournalId, eJournal.eJournalTitle, eJournal.eJournalURL, eJournal.eJournalCoverage, eJournal.eJournalSubjectId, Source.SourceTitle,
eJournal.ejournalAlsoAvailable, eJournal.ejournalAlsoAvailable + Source.SourceTitle AS SOURCE
FROM eJournal LEFT OUTER JOIN
Source ON Source.SourceId = eJournal.eJournalSourceId
ORDER BY eJournal.eJournalTitle

the bold stuff is creating problem for me

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-12-23 : 06:32:35

Use Isnull or coalesce

SELECT eJournal.eJournalId, eJournal.eJournalTitle, eJournal.eJournalURL, eJournal.eJournalCoverage, eJournal.eJournalSubjectId, Source.SourceTitle,
eJournal.ejournalAlsoAvailable,isnull( eJournal.ejournalAlsoAvailable,'') + isnull(Source.SourceTitle,'') AS SOURCE
FROM eJournal LEFT OUTER JOIN
Source ON Source.SourceId = eJournal.eJournalSourceId
ORDER BY eJournal.eJournalTitle

Kirun
Yak Posting Veteran

60 Posts

Posted - 2009-12-23 : 07:08:24

Thank you so much.. its done.

wondering if i can use an if statement in this query as i like to merge two colums with a commma like this

SELECT eJournal.eJournalId, eJournal.eJournalTitle, eJournal.eJournalURL, eJournal.eJournalCoverage, eJournal.eJournalSubjectId, ISNULL(Source.SourceTitle, '') + ',' + ISNULL(eJournal.ejournalAlsoAvailable, '') AS SOURCE FROM eJournal INNER JOIN Source ON eJournal.eJournalSourceId = Source.SourceId ORDER BY eJournal.eJournalTitle

but problem is that when the value is null it display a value and a comma.like abc, I know i can manipulate in my code but just wondering if i can do this in SQL

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-23 : 07:16:19

You can use a case statement


SELECT
	eJournal.eJournalId
	, eJournal.eJournalTitle
	, eJournal.eJournalURL
	, eJournal.eJournalCoverage
	, eJournal.eJournalSubjectId
	, ISNULL(Source.SourceTitle, '')
		+ CASE	
			WHEN source.sourceTitle IS NULL OR eJournal.ejournalAlsoAvailable IS NULL THEN ''
			ELSE ','
		END
		+ ISNULL(eJournal.ejournalAlsoAvailable, '') AS SOURCE
FROM
	eJournal
	INNER JOIN Source ON eJournal.eJournalSourceId = Source.SourceId
ORDER BY
	eJournal.eJournalTitle

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 07:40:01

<<
case statement
>>

It should be rightly called as CASE EXPRESSION

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-23 : 07:49:16

u can try like this


SELECT
	eJournal.eJournalId
	, eJournal.eJournalTitle
	, eJournal.eJournalURL
	, eJournal.eJournalCoverage
	, eJournal.eJournalSubjectId
	, ISNULL(Source.SourceTitle+',', '')
		+ ISNULL(eJournal.ejournalAlsoAvailable, '') AS SOURCE
FROM
	eJournal
	INNER JOIN Source ON eJournal.eJournalSourceId = Source.SourceId
ORDER BY
	eJournal.eJournalTitle

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-23 : 08:55:49

quote:
Originally posted by madhivanan

<<
case statement
>>

It should be rightly called as CASE EXPRESSION

Madhivanan

Failing to plan is Planning to fail

LOL. SOOOORRRRRRRRREEEEYYY!

I guess the documentation does refer to it as a CASE function....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kirun
Yak Posting Veteran

60 Posts

Posted - 2009-12-24 : 01:55:03

@Transact Charlie: your query works fine,thanks
@bklr: i tried your one two but it still give me comma

Thank you all for helping and for your time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-24 : 02:01:23

<<
I guess the documentation does refer to it as a CASE function....
>>

Yes. But it lacks what a function does. So it is a expression

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-24 : 04:47:01

quote:
Originally posted by madhivanan

<<
I guess the documentation does refer to it as a CASE function....
>>

Yes. But it lacks what a function does. So it is a expression

Madhivanan

Failing to plan is Planning to fail

I think in future I'll just say

"You can use CASE".... and then let the reader mentally substitute their own description...

Happy holidays Madhivanan

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-24 : 05:14:47

<<
Happy holidays Madhivanan
>>

Thanks and wishing you the same

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources