March 6, 2007 at 1:01 pm
i need help on this .i am getting following error message when i execute the stored procedure below:
-------------------------------------------------------------------------------
(10 row(s) affected)
(25 row(s) affected)
Msg 207, Level 16, State 1, Procedure COSTGetUniformatProjectPhase , Line 59
Invalid column name 'Level1'.
Msg 207, Level 16, State 1, Procedure COSTGetUniformatProjectPhase , Line 59
Invalid column name 'Level1'.
Msg 207, Level 16, State 1, Procedure COSTGetUniformatProjectPhase , Line 59
Invalid column name 'Lvl2'.
Msg 207, Level 16, State 1, Procedure COSTGetUniformatProjectPhase , Line 59
Invalid column name 'Lvl2'.
----------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: < >
-- Create date: < >
-- Description: < >
-- =============================================
ALTER
Procedure [dbo].[COSTGetUniformatProjectPhase ]
@MSBA_ID
varchar(10),
@Projet_Phase_ID
int
AS
/*
*/
/*
-- Get Uniformat data.
Select Uniformat_Category_ID,
Uniformat_Cost,
Uniformat_Distribution_Percent,
Uniformat_Rate
From COST_Project_Phase_Uniformat
Where MSBA_ID = @MSBA_ID
And Project_Phase_ID = @Projet_Phase_ID
*/
-- Rollup to level 1
Select
CUC.Rollup_To_Level1,
Sum(Case When Uniformat_Cost is Null then 0 Else Uniformat_Cost End) Uniformat_Cost
Into
#Sum_Level1
From
COST_Uniformat_Category CUC
Left
Outer Join COST_Project_Phase_Uniformat
On CUC.Uniformat_Category_ID = COST_Project_Phase_Uniformat.Uniformat_Category_ID
where
CUC.Uniformat_Category_Level = 2
Group
By CUC.Rollup_To_Level1
-- Rollup to level 2
Select
CUC.Rollup_To_Level2,
Sum(Case When Uniformat_Cost is Null then 0 Else Uniformat_Cost End) Uniformat_Cost
Into
#Sum_Level2
From
COST_Uniformat_Category CUC
Left
Outer Join COST_Project_Phase_Uniformat
On CUC.Uniformat_Category_ID = COST_Project_Phase_Uniformat.Uniformat_Category_ID
where
CUC.Uniformat_Category_Level in (2,3)
Group
By CUC.Rollup_To_Level2
-- Return Set.
Select
0 Uniformat_Category_ID,
'0' Uniformat_Category_Code,
'0' Uniformat_Category_Name,
0 Uniformat_Category_Level
,
'0' Parent,
Sum(Uniformat_Cost) Uniformat_Cost
From
#Sum_Level1
Union
-- Level 1 Data.
Select
CUC.Uniformat_Category_ID,
CUC
.Uniformat_Category_Code,
CUC
.Uniformat_Category_Name,
CUC
.Uniformat_Category_Level,
'0' Parent,
Case
When Level1 is Null then 0 Else Level1 End Level1
From
COST_Uniformat_Category CUC
Left
Outer Join #Sum_Level1 Lvl1
On Lvl1.Rollup_To_Level1 = CUC.Uniformat_Category_Code
Where
CUC.Uniformat_Category_Level = 1
Union
-- Level 2 Data.
Select
CUC.Uniformat_Category_ID,
CUC
.Uniformat_Category_Code,
CUC
.Uniformat_Category_Name,
CUC
.Uniformat_Category_Level,
CUC
.Rollup_To_Level1 Parent,
Case
When Lvl2 is Null then 0 Else Lvl2 End Lvl2
From
COST_Uniformat_Category CUC
Left
Outer Join COST_Project_Phase_Uniformat
On CUC.Uniformat_Category_ID = CUC.Uniformat_Category_ID
Where
CUC.Uniformat_Category_Level = 2
-- Level 3 Data
Select
CUC.Uniformat_Category_ID,
CUC
.Uniformat_Category_Code,
CUC
.Uniformat_Category_Name,
CUC
.Uniformat_Category_Level,
CUC
.Rollup_To_Level1 Parent
From
COST_Uniformat_Category CUC
Left
Outer Join COST_Project_Phase_Uniformat
On CUC.Uniformat_Category_ID = CUC.Uniformat_Category_ID
Where
CUC.Uniformat_Category_Level = 3
March 6, 2007 at 2:28 pm
The message appears quite clear that the columns named 'Level1' and 'Lvl2' are not valid.
This can mean that the tables/views do not contain these columns or that your login does not have security privileges to select these columns.
SQL = Scarcely Qualifies as a Language
March 6, 2007 at 5:37 pm
The error shown refers to my case statement where i am using the column name 'Level1' and 'Lvl2' .The thing i dont understand which column name should i use.follwing is the structure of the two tables relating to the stored procedure.
---------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COST_Uniformat_Category](
[Uniformat_Category_ID] [int] IDENTITY(1,1) NOT NULL,
[Uniformat_Category_Code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Uniformat_Category_Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Uniformat_Category_Level] [int] NULL,
[Rollup_To_Level1] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Valid_Flag] [bit] NULL,
[Rollup_To_Level2] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [XPKCOST_Uniformat_Category] PRIMARY KEY NONCLUSTERED
(
[Uniformat_Category_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[COST_Project_Phase_Uniformat](
[Uniformat_Category_ID] [int] NOT NULL,
[Uniformat_Cost] [money] NULL,
[Uniformat_Distribution_Percent] [decimal](10, 4) NULL,
[Uniformat_Rate] [money] NULL,
[UoM_ID] [int] NULL,
[Uniformat_Note] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Last_Updated_Dt] [datetime] NULL,
[Last_Updated_By] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MSBA_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Project_Phase_ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [XPKCOST_Project_Phase_Uniformat] PRIMARY KEY NONCLUSTERED
(
[MSBA_ID] ASC,
[Project_Phase_ID] ASC,
[Uniformat_Category_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<script></script>
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
March 6, 2007 at 10:20 pm
Neither table contains a column named "Level1".
Neither table contains a column named "Lvl2".
There error is telling you exactly what's wrong. You are using column names that don't exist.
March 8, 2007 at 8:57 am
Hi ,
The columns u have used in the query r not valid.
Please verify. The column names u have used..
Regards,
Amit Gupta..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply