January 17, 2013 at 8:33 am
Hello,
I have a fn and a sp, the fn returns table and the sp retrieves info from the table-fn.
Both are working fine in sql2k5, when I migrate the db to 2k8, it returns me with error saying some fields are not valid.
Here is the fn:
Create function [dbo].[fnGetAllClassificationRanking]()
Returns TABLE
AS
RETURN ( Select distinct i.SysID, Code, i.Name
, Integrity = case when i.Integrity > 0 then ig.ranking else 'Unknown' end
, Availability = case when i.Availability > 0 then a.ranking else 'Unknown' end
, Confidentiality = case when i.Confidentiality > 0 then cf.ranking else 'Unknown' end
, Continuity = case when i.Continuity > 0 then ct.ranking else 'Unknown' end
, i.Status, i.Type
From infosys i
left outer join Integrity ig on ig.id = i.integrity
left outer join Availability a on a.id = i.Availability
left outer join Confidentiality cf on cf.id = i.Confidentiality
left outer join Continuity ct on ct.id = i.Continuity
)
The fn is created with no issue.
Here is the sp:
CREATE proc [dbo].[spCustomizeRankingResult](
@confidentiality varchar(50),
@continuity varchar(50),
@integrity varchar(50),
@availability varchar(50))
AS
SELECT distinct sysid, code, [name] as SA, integrity, confidentiality,
availability, continuity, [type], [status]
FROM AOR.[dbo].[fnGetAllClassificationRanking]() c
where
confidentiality like case when @confidentiality = 'all' then '%' else @confidentiality end
and
continuity like case when @continuity = 'all' then '%' else @continuity end
and
integrity like case when @integrity = 'all' then '%' else @integrity end
and
availability like case when @availability = 'all' then '%' else @availability end
order by code
GO
The error says:
Msg 207, Level 16, State 1, Procedure spCustomizeRankingResult, Line 9
Invalid column name 'type'.
Msg 207, Level 16, State 1, Procedure spCustomizeRankingResult, Line 9
Invalid column name 'status'.
What am I missing? do I need to rewrite the code in SQL2k8?
Thanks.
January 17, 2013 at 9:32 am
Problem identified, removing prefix in FROM AOR.[dbo].[fnGetAllClassificationRanking]() c resolves the issue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply