String concat and views

[outofservice]

Version: SQL2k Standard Edition
No service pack applied.

Basically, I have a view (taken from two tables) which contains an 'address' column containing the concatenated values of 3 existing columns (number, street and area).

My problem is that when I display the view in Enterprise manager I cannot see the concatenation in effect and the 'address' column returns 'null' values. When I display the view in query analyzer I can see the correct concatenated 'address' values as eg, 1, High, st.

Does anybody know why this is?


Lauryn Bradley
SQL Server DBA

 

[MAK9974]

Your query analyse is set with (Connection properties)

SET CONCAT_NULL_YIELDS_NULL OFF

 

[outofservice]

PS, this is the code I am using.

CREATE VIEW a_view AS
SELECT

aeven.eid,
aeven.num_1,
event.tycod,
event.sub_tycod,
aeven.dgroup,
(LTRIM (
event.eapt + SPACE(1) +
event.estnum + SPACE(1) +
event.efeanme + SPACE(1) +
event.efeatyp + SPACE(1) +
event.edirsuf
))AS Location
FROM aeven LEFT OUTER JOIN event
ON aeven.eid = event.eid Lauryn Bradley
SQL Server DBA

 

[outofservice]

I have already set SET CONCAT_NULL_YIELDS_NULL OFF so that cant be the reason why. Lauryn Bradley
SQL Server DBA

 

[Drorg]

try using isnull() for each field in the concat string
for example: ISNULL(event.eapt,'') + SPACE(1) +
ISNULL(event.estnum,'') + SPACE(1).......

 

[outofservice]

Thanks! Thats solved my problem nicely! Lauryn Bradley
SQL Server DBA