WITH PageCatTree ( ID, ParentID, ParentName, DomainID,CreatedByID,CreatedDate,ModifiedByID,ModifiedDate,DeletedByID,DeletedDate,IsDeleted,LanguageID,[Name],PageUrl,[Order],IsActive,IndexNo,IndexLevel,IndexLevel2) AS
(
SELECT
tPageCategory.ID,
tPageCategory.ParentId,
CASE
WHEN tPageCategory.ParentID = 0 THEN ''
ELSE (Select [Name] From tPageCategory_Detail Where PageCategoryID = tPageCategory.ParentID AND LanguageId = 1 )
END AS ParentName,
tPageCategory.DomainID,
tPageCategory.CreatedByID,
tPageCategory.CreatedDate,
tPageCategory.ModifiedByID,
tPageCategory.ModifiedDate,
tPageCategory.DeletedByID,
tPageCategory.DeletedDate,
tPageCategory.IsDeleted,
tPageCategory_Detail.LanguageID,
--tPageCategory_Detail.Name,
CASE
WHEN tPageCategory.ParentID<>0 THEN tPageCategory_Detail.Name
ELSE tPageCategory_Detail.Name
END AS Name,
tPageCategory_Detail.PageUrl,
tPageCategory_Detail.[Order],
tPageCategory_Detail.IsActive,
CAST(ROW_NUMBER() OVER (ORDER BY tPageCategory_Detail.[Order] ASC) as varchar(255)),
CAST(ROW_NUMBER() OVER (ORDER BY tPageCategory_Detail.[Order] ASC) as INT),
CAST(ROW_NUMBER() OVER (ORDER BY tPageCategory_Detail.[Order] ASC) as DECIMAL(10,2))
FROM
tPageCategory
INNER JOIN tPageCategory_Detail ON tPageCategory.ID = tPageCategory_Detail.PageCategoryId
WHERE
tPageCategory.ParentID = 0
AND tPageCategory.IsDeleted = 0
AND tPageCategory.DomainID = 1
AND tPageCategory_Detail.LanguageID = 1
UNION ALL
SELECT
tPageCategory.ID,
tPageCategory.ParentId,
CASE
WHEN tPageCategory.ParentID=0 THEN ''
ELSE (Select [Name] From tPageCategory_Detail Where PageCategoryID = tPageCategory.ParentID AND LanguageId = 1 )
END AS ParentName,
tPageCategory.DomainID,
tPageCategory.CreatedByID,
tPageCategory.CreatedDate,
tPageCategory.ModifiedByID,
tPageCategory.ModifiedDate,
tPageCategory.DeletedByID,
tPageCategory.DeletedDate,
tPageCategory.IsDeleted,
tPageCategory_Detail.LanguageID,
--tPageCategory_Detail.Name,
CASE
WHEN tPageCategory.ParentID<>0 THEN tPageCategory_Detail.Name
ELSE tPageCategory_Detail.Name
END AS Name,
tPageCategory_Detail.PageUrl,
tPageCategory_Detail.[Order],
tPageCategory_Detail.IsActive,
CAST(PageCatTree.IndexNo +'.' + CAST(ROW_NUMBER() OVER (ORDER BY tPageCategory_Detail.[Order] ASC) as varchar(255)) as varchar(255)) ,
CAST(PageCatTree.IndexNo as INT) ,
CASE
WHEN (ROW_NUMBER() OVER (ORDER BY tPageCategory_Detail.[Order] ASC)) < 10 THEN
CONVERT(DECIMAL(10,2), CAST(PageCatTree.IndexNo + '.0' + CAST(ROW_NUMBER() OVER (ORDER BY tPageCategory_Detail.[Order] ASC) AS VARCHAR(255)) AS VARCHAR(255)))
ELSE
CONVERT(DECIMAL(10,2), CAST(PageCatTree.IndexNo + '.' + CAST(ROW_NUMBER() OVER (ORDER BY tPageCategory_Detail.[Order] ASC) AS VARCHAR(255)) AS VARCHAR(255)))
END
FROM
tPageCategory
INNER JOIN tPageCategory_Detail ON tPageCategory.ID = tPageCategory_Detail.PageCategoryId
JOIN PageCatTree ON PageCatTree.ID = tPageCategory.ParentID
WHERE
tPageCategory.IsDeleted = 0
AND tPageCategory.DomainID = 1
AND tPageCategory_Detail.LanguageID = 1
)
SELECT * FROM PageCatTree ORDER BY IndexLevel ASC, IndexLevel2 ASC, IndexNo ASC, [Order]
Yukarıdaki sorgumda beklenmedik bir hata aldım nasıl çözecegimi bilmiyorum benden önce çalışan arakadaşlar yazmışlar
Hata : Conversion failed when converting the varchar value '1.3' to data type int.
