CONCAT_NULL_YIELDS_NULL in a View

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

SQL Server Development (2000)

CONCAT_NULL_YIELDS_NULL in a View

Author

Topic

Noureldin
Starting Member

14 Posts

Posted - 2005-08-02 : 17:18:43

Upon making a view, I tried to add:

SET CONCAT_NULL_YIELDS_NULL OFF

in the view design, because it retreives a column from another view, where that column concatunates several columns from a third table where there might occur an empty column string value

For example

TABLE1:

first_name: Amr
middle_name: <NULL>
last_name: Noureldin

VIEW1:
Name: first_name + ' ' + middle_name + ' ' + last_name

VIEW2: retreives column Name from VIEW1

in VIEW2, I want to: SET CONCAT_NULL_YIELDS_NULL OFF
but it doesn't work.. Is there any other alternative solution?

thekeane
Starting Member

7 Posts

Posted - 2005-08-02 : 17:30:39

Not sure if it will work, but when you create the view you could use the IsNull function and replace a null with a space

IsNull(first_name, ' ') + IsNull(Middle_Name, ' ') + IsNull(Last_Name, ' ')

Sorry don't have a way to test it here right now, but logic seems sound.

Ravenn
Starting Member

7 Posts

Posted - 2005-08-02 : 17:32:12

ISNULL(Mycoolcolumn,'')

Noureldin
Starting Member

14 Posts

Posted - 2005-08-02 : 17:36:51

Well, I have my view design as follows:

SELECT dbo.movie.movie_name, dbo.actor_vw.Name
FROM dbo.movie_actor_star INNER JOIN
dbo.movie ON dbo.movie_actor_star.movie_id = dbo.movie.movie_id INNER JOIN
dbo.actor_vw ON dbo.movie_actor_star.actor_id = dbo.actor_vw.actor_id

Where shall I insert the IsNull tag?

I tried at the end, didn't work

thekeane
Starting Member

7 Posts

Posted - 2005-08-02 : 17:38:18

Go back a level to where you are creating the actor_vw.Name column and put it there. Doesn't look like you are doing any concatenating in that query.

Noureldin
Starting Member

14 Posts

Posted - 2005-08-02 : 17:41:07

Perfect, I realized so and corrected it. And it worked :)

Thanks alot guys :)

thekeane
Starting Member

7 Posts

Posted - 2005-08-02 : 17:42:14

I like it when code works. So much less frustrating. ;)