[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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
0
LJG
Asked:
LJG
1 Solution
 
AdamSenior DeveloperCommented:
Some sample data and the schemas of the tables would be useful
0
 
LowfatspreadCommented:
not sure you stated the problem correctly...

but in the view the properties table is left outer joined ... so may not be present in which case the prop name property table components would return null resulting in a null value for propname,,,
0
 
LJGAuthor Commented:
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
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now