Joining TVF to a view

Question

Joining TVF to a view

sbishnoi

SSC Journeyman

Points: 85
More actions
September 17, 2010 at 6:10 pm

#387814

I have an existing view1(vewsearchcategory) and now i need to add the columns from the TVF to this view. I am trying to do a join between the view and the tvf but its not happening. So i was thinking of creating a new view where i can join the view1 and the TVF. i know its not the best approch but even this is not happening...:(
The view1 is : vewsearchcategory
IF EXISTS (SELECT Name FROM sysobjects WHERE Name = 'vewSearchCategory' AND type = 'V')
DROP VIEW vewSearchCategory
GO
CREATE VIEW vewSearchCategory AS
SELECT Category.CMSID CategoryGUID,
CategoryType.CategoryTypeName,
DisplayName Title,
dbo.sfGetCategoryDescfmID(Category.CategoryID) [Description],
dbo.sfGetPriceList(Category.CMSID, null) PricelistKey,
replace(dbo.sfGetCategoryPathFmID(Category.CategoryID,'/'),'&','&') CategoryPath,
'US' Country,
BIASweight,
Display.LanguageCode Language,
dbo.sfKeywordsToString(null, Category.CategoryID, null, null, null) Keywords,
Category.UpdateDate UpdateTime,
0 DeleteFlag,
replace(dbo.sfGetCategoryNamefmID (ParentCategoryXref.ParentCategoryID),'&','&') ParentCategoryName,
dbo.sfGetCMSIDfmCategoryID (ParentCategoryXref.ParentCategoryID) ParentCategoryGUID,
replace(dbo.sfGetCategoryNamefmID (GrandParentCategoryXref.ParentCategoryID),'&','&') GrandParentCategoryName,
dbo.sfGetCMSIDfmCategoryID (GrandParentCategoryXref.ParentCategoryID) GrandParentCategoryGUID,
dbo.sfGetBioRadVerticalfmCategoryID (Category.CategoryID, 3) DivisionName,
dbo.sfGetBioRadVerticalfmCategoryID (Category.CategoryID, 2) DivisionShortName
FROM dbo.Category ,
dbo.Display,
dbo.CategoryType,
dbo.CategoryXref ParentCategoryXref
LEFT OUTER JOIN dbo.CategoryXref GrandParentCategoryXref
ON ParentCategoryXref.ParentCategoryID = GrandParentCategoryXref.CategoryID
INNER JOIN dbo.sfGetPathLevelfmCategoryID (category.categoryid) AS PLC
ON PLC.CMSID = CATEGORY.CMSID
WHERE Category.CategoryID = Display.CategoryID
AND Category.CategoryID = ParentCategoryXref.CategoryID
AND Display.LanguageCode = 'EN'
AND Category.CategoryTypeID = CategoryType.CategoryTypeID
AND CategoryType.CategoryTypeName = 'Category'
AND LogicalDeleteFlag = 0
GO
The TVF :
IF EXISTS (SELECT Name FROM sysobjects WHERE Name = 'sfGetPathLevelfmCategoryID' AND type = 'TF')
DROP FUNCTION dbo.sfGetPathLevelfmCategoryID
GO
CREATE FUNCTION dbo.sfGetPathLevelfmCategoryID (@CMSID varchar(50))
RETURNS @Levels TABLE (
CMSID varchar(50),
CategoryPath nvarchar(max),
CategoryLevel0 nvarchar(1000),
CategoryLevel1 nvarchar(1000),
CategoryLevel2 nvarchar(1000),
CategoryLevel3 nvarchar(1000),
CategoryLevel4 nvarchar(1000),
CategoryLevel5 nvarchar(1000),
CategoryLevel6 nvarchar(1000),
CategoryLevel7 nvarchar(1000),
CategoryLevel8 nvarchar(1000),
CategoryLevel9 nvarchar(1000),
CategoryLevel10 nvarchar(1000)
)
AS
BEGIN
DECLARE @CategoryID INT
SELECT @CategoryID = CategoryID
FROM dbo.Category
WHERE CMSID = @CMSID
DECLARE @CategoryPath nvarchar(max)
SELECT @CategoryPath = replace(dbo.sfGetCategoryPathFmID(@CategoryID,'/'),'&','&')
BEGIN
INSERT INTO @Levels
(
CMSID ,
CategoryPath,
CategoryLevel0 ,
CategoryLevel1 ,
CategoryLevel2 ,
CategoryLevel3 ,
CategoryLevel4 ,
CategoryLevel5 ,
CategoryLevel6 ,
CategoryLevel7 ,
CategoryLevel8 ,
CategoryLevel9 ,
CategoryLevel10
)
SELECT
@CMSID,
@CategoryPath,
[Level0],
[Level1],
[Level2],
[Level3],
[Level4],
[Level5],
[Level6],
[Level7],
[Level8],
[Level9],
[Level10]
FROM ( SELECT [LevelNum],
CategoryName
FROM dbo.sfSearchGetCategoryLevel (REPLACE(REPLACE(@CategoryPath,'/Home/',''),'Products/',''),'/')
)as p
PIVOT ( max(CategoryName)
FOR LevelNum IN ([Level0],[Level1],[Level2],[Level3],[Level4],[Level5],[Level6],[Level7],[Level8],[Level9],[Level10])
) AS pvt
END
RETURN
END
GO
Can you please help me in solving this issue. The result should have the columns comming from the view1 : vewsearchcategory and the 10 categorylevels.
Please help...need it urgentlly!!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

winash SSChampion Points: 11446 More actions · Answer 1

I don't see you selecting columns from the TVF in the view - there is a join to the TVF but you aren't selecting any of the TVF columns in the view. Did you try selecting these columns in the view and seeing if it returns data?

sbishnoi SSC Journeyman Points: 85 More actions · Answer 2

i was trying to get the columns in the TVF :CMSID ,

CategoryPath,

CategoryLevel0 ,

CategoryLevel1 ,

CategoryLevel2 ,

CategoryLevel3 ,

CategoryLevel4 ,

CategoryLevel5 ,

CategoryLevel6 ,

CategoryLevel7 ,

CategoryLevel8 ,

CategoryLevel9 ,

CategoryLevel10

added to the view1...so finally i have created another view which calls the view1 and the TVF and am joining using OUTER APPLY....so for now i am getting all the columns of view1 and all the columns of TVF in one view.

Let me know if there is any other good approch besides this.

Thanks,

sbishnoi SSC Journeyman Points: 85 More actions · Answer 3

Sorry forgot to post the query for the new view which calls the view1 and the TVF:

CREATE VIEW vewFullSearchCategory AS

SELECT SPD.*,PLC.*

FROM vewSearchCategory AS SPD

OUTER APPLY sfGetPathLevelfmCategoryID(SPD.CategoryGUID) AS PLC