Link to home
Start Free TrialLog in
Avatar of LJG
LJG

asked on

View based on view with concatinated fileld returns a null value


Why does the below view based on vw_1 bring back a null value (Note: Vw_1 does not return null value on Prop_Name on any row)  - SQL Server 2008

----------- Prop Name Returns Null Value --------------
SELECT     Prop_Name, PropID  FROM         dbo.vw_1
GROUP BY Prop_Name, PropID

----------- Prop Name >>Does Not<< Returns Null Value --------------
SELECT     Prop_Name  FROM         dbo.vw_1
GROUP BY Prop_Name

----------- Prop_Name  --------------
dbo.tbl_Properties.PropName + N': ' + dbo.tbl_Properties.PropAddress + N': zip-' + LEFT(ISNULL(dbo.tbl_Properties.PropZip, N'') + '000', 3) AS Prop_Name

----------- Entire View #1  --------------
SELECT     TOP (100) PERCENT dbo.tbl_Properties.PropName + N': ' + dbo.tbl_Properties.PropAddress + N': zip-' + LEFT(ISNULL(dbo.tbl_Properties.PropZip, N'')
                      + '000', 3) AS Prop_Name, dbo.tbl_Properties.PropID, 1 AS Root, LEFT(ISNULL(dbo.tbl_Properties.PropZip, N'') + '000', 3) AS PropZip_3Digit,
                      dbo.tbl_Prop_Agreements.PropAgr_StatusIdd, dbo.tbl_Properties.Prop_StatusIdd
FROM         dbo.tbl_Prop_Agreements LEFT OUTER JOIN
                      dbo.tbl_Prop_Surfaces ON dbo.tbl_Prop_Agreements.PropSurfaceIdd = dbo.tbl_Prop_Surfaces.PropSurfaceID LEFT OUTER JOIN
                      dbo.tbl_Properties ON dbo.tbl_Prop_Surfaces.PropIdd = dbo.tbl_Properties.PropID
WHERE     (dbo.tbl_Properties.PropID > 0)

Thanks in advance for any help.
LJG
Avatar of Adam
Adam
Flag of United Kingdom of Great Britain and Northern Ireland image

Some sample data and the schemas of the tables would be useful
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LJG
LJG

ASKER

Lowfatspread:
Thanks - you led me to the answer:

The problem was that .tbl_Properties.PropAddress was null - that caused the concatenated field Prop_Name to be Null

----------- Prop_Name  --------------
dbo.tbl_Properties.PropName + N': ' + dbo.tbl_Properties.PropAddress + N': zip-' + LEFT(ISNULL(dbo.tbl_Properties.PropZip, N'') + '000', 3) AS Prop_Name

Thanks
LJG