CASE Order By problem ?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CASE Order By problem ??

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2001-06-21 : 07:53:59

In the code below can someone please explain why i get this error message when try to save this stored procedure ? It's something to do with the CASE statement i think but i can't work out exactly what ? Line 27 and line 96 ?, am i missing a comma or something that separates the rows ? Many thanks.

Server: Msg 207, Level 16, State 3, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalShares1'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalDate1'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalShares2'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalDate2'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalShares3'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalDate3'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalShares4'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalDate4'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalShares5'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalDate5'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalShares6'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'IntervalDate6'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 37
Invalid column name 'InstName'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalShares1'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalDate1'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalShares2'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalDate2'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalShares3'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalDate3'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalShares4'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalDate4'.
Server: Msg 207, Level 16, State 3, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalShares5'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalDate5'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalShares6'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'IntervalDate6'.
Server: Msg 207, Level 16, State 1, Procedure usp_ShareHolders_FDC_Inst_History_sel_test, Line 96
Invalid column name 'InstName'.

CREATE PROCEDURE usp_ShareHolders_FDC_Inst_History_sel_test

@cusipCode varchar(8),
@SortBy tinyint,
@SortOrder varchar(1)

AS

DECLARE
@intErrorCode int,
@TransactionCountOnEntry int

-- Error handling code
SELECT @intErrorCode = @@Error

If @intErrorCode = 0
Begin
SELECT @TransactionCountOnEntry = @@TranCount
BEGIN TRANSACTION
End

-- QUARTERLY INTERVALS

If @intErrorCode = 0
Begin
If (@SortOrder) = 'A'
BEGIN
SELECT
M.name as Institution,
M.org_key as FMCode,
I.P1_period as IntervalDate1,
I.P1_perc_shout as IntervalShares1,
I.P2_period as IntervalDate2,
I.P2_perc_shout as IntervalShares2,
I.P3_period as IntervalDate3,
I.P3_perc_shout as IntervalShares3,
I.P4_period as IntervalDate4,
I.P4_perc_shout as IntervalShares4,
I.P5_period as IntervalDate5,
I.P5_perc_shout as IntervalShares5,
I.P6_period as IntervalDate6,
I.P6_perc_shout as IntervalShares6,
P.Proteus_FMG_Code as Inst_Code,
A.fa_Inst_addressCode as Inst_Address_Code
FROM
FDC_Institutional_Ownership_Archive I
INNER JOIN
FDC_Organization_Mapping_Record O
ON
I.org_key = O.id_1
INNER JOIN
FDC_Management_Company_Record M
ON
O.id_2 = M.org_key
INNER JOIN
Map_FDC_for_Profiles P
ON
M.org_key = P.FDC_Org_Key
INNER JOIN
tbl_Inst_Address A
ON
P.Proteus_FMG_Code = A.fi_InstitutionCode
WHERE
I.cusip = @cusipCode
ORDER BY
Case @SortBy
When 2 Then IntervalShares1
When 3 Then IntervalDate1
When 4 Then IntervalShares2
When 5 Then IntervalDate2
When 6 Then IntervalShares3
When 7 Then IntervalDate3
When 8 Then IntervalShares4
When 9 Then IntervalDate4
When 10 Then IntervalShares5
When 11 Then IntervalDate5
When 12 Then IntervalShares6
When 13 Then IntervalDate6
End Asc,
Case @SortBy
When 1 Then Institution
End Asc
END

If (@SortOrder) = 'D'
BEGIN
SELECT
M.name as Institution,
M.org_key as FMCode,
I.P1_period as IntervalDate1,
I.P1_perc_shout as IntervalShares1,
I.P2_period as IntervalDate2,
I.P2_perc_shout as IntervalShares2,
I.P3_period as IntervalDate3,
I.P3_perc_shout as IntervalShares3,
I.P4_period as IntervalDate4,
I.P4_perc_shout as IntervalShares4,
I.P5_period as IntervalDate5,
I.P5_perc_shout as IntervalShares5,
I.P6_period as IntervalDate6,
I.P6_perc_shout as IntervalShares6,
P.Proteus_FMG_Code as Inst_Code,
A.fa_Inst_addressCode as Inst_Address_Code
FROM
FDC_Institutional_Ownership_Archive I
INNER JOIN
FDC_Organization_Mapping_Record O
ON
I.org_key = O.id_1
INNER JOIN
FDC_Management_Company_Record M
ON
O.id_2 = M.org_key
INNER JOIN
Map_FDC_for_Profiles P
ON
M.org_key = P.FDC_Org_Key
INNER JOIN
tbl_Inst_Address A
ON
P.Proteus_FMG_Code = A.fi_InstitutionCode
WHERE
I.cusip = @cusipCode
ORDER BY
Case @SortBy
When 2 Then IntervalShares1
When 3 Then IntervalDate1
When 4 Then IntervalShares2
When 5 Then IntervalDate2
When 6 Then IntervalShares3
When 7 Then IntervalDate3
When 8 Then IntervalShares4
When 9 Then IntervalDate4
When 10 Then IntervalShares5
When 11 Then IntervalDate5
When 12 Then IntervalShares6
When 13 Then IntervalDate6
End Desc,
Case @SortBy
When 1 Then Institution
End Desc
END
End

--Error handling code
If @@TranCount > @TransactionCountOnEntry
Begin
If @@Error = 0
COMMIT TRANSACTION -- if no errors were encountered then COMMIT
Else
ROLLBACK TRANSACTION -- if errors encountered then ROLLBACK
End

Edited by - KnooKie on 06/21/2001 08:15:22

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources