case statement problems in the order by clause

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

Transact-SQL (2000)

case statement problems in the order by clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-02 : 07:31:26

Cheri writes "I have an order by clause that uses case statements. When I try to select distinct(Inforce.Policy#), SQL says "Inforce.Policy#" must be in the order by. It is, see code below.

Has anyone else run across this? It seems to not see any criteria after the first case statement. Is there a work around or fix for this if it is a SQL bug?

Code is as shown below:

CREATE PROCEDURE [sp_InforceAgtListHOwithSupAgt] @strAgtNum varchar(7), @InsName varchar(25), @AgtName varchar(25), @State varchar(2), @ZipCode varchar(5), @ProdLine varchar(2), @Birthday varchar(20), @SortColumn varchar(1), @Sort varchar(1), @FieldOffice varchar(10)
WITH RECOMPILE
AS
if @Sort = '1'
SELECT Inforce.[Policy#], Inforce.InsuredName, Inforce.Status, Inforce.Productline, Inforce.[Plan], Inforce.writeagent, Inforce.[Ef Date] effdate, Agent.[Agent#], Agent.AgentName
FROM Agent, Inforce
where Agent.[Policy#] = Inforce.[Policy#]
and Agent.[Agent#] LIKE @strAgtNum
and Inforce.InsuredName LIKE @InsName
and Agent.AgentName LIKE @AgtName
and Inforce.St LIKE @State
and Inforce.Zip LIKE @ZipCode
and Inforce.Productline LIKE @ProdLine
and Inforce.DOB LIKE @Birthday
and Inforce.office LIKE @FieldOffice
ORDER BY
case when @SortColumn = '1' then Inforce.[Policy#] end DESC,
case when @SortColumn = '2' then Inforce.InsuredName end DESC,
case when @SortColumn = '3' then Inforce.Status end DESC,
case when @SortColumn = '4' then Inforce.[Plan] end DESC,
case when @SortColumn = '5' then Inforce.writeagent end DESC,
case when @SortColumn = '6' then Agent.AgentName end DESC,
case when @SortColumn = '7' then Inforce.[Ef Date] end DESC,
Inforce.[Policy#] DESC
else
SELECT Inforce.[Policy#], Inforce.InsuredName, Inforce.Status, Inforce.Productline, Inforce.[Plan], Inforce.writeagent, Inforce.[Ef Date] effdate, Agent.[Agent#], Agent.AgentName
FROM Agent, Inforce
where Agent.[Policy#] = Inforce.[Policy#]
and Agent.[Agent#] LIKE @strAgtNum
and Inforce.InsuredName LIKE @InsName
and Agent.AgentName LIKE @AgtName
and Inforce.St LIKE @State
and Inforce.Zip LIKE @ZipCode
and Inforce.Productline LIKE @ProdLine
and Inforce.DOB LIKE @Birthday
and Inforce.office LIKE @FieldOffice
ORDER BY
case when @SortColumn = '1' then Inforce.[Policy#] end ASC,
case when @SortColumn = '2' then Inforce.InsuredName end ASC,
case when @SortColumn = '3' then Inforce.Status end ASC,
case when @SortColumn = '4' then Inforce.[Plan] end ASC,
case when @SortColumn = '5' then Inforce.writeagent end ASC,
case when @SortColumn = '6' then Agent.AgentName end ASC,
case when @SortColumn = '7' then Inforce.[Ef Date] end ASC,
Inforce.[Policy#] ASC
GO"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-02 : 07:39:47

All the columns that appear in the Order by clause must be selected in the Select Statement if you use Distinct

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-02 : 08:10:37

Be careful also with the use of the "WITH RECOMPILE" option. You may/will lose the benefit of "plan caching" and slow down execution...as it's good/important to specify the tables with "dbo." before them for this as well.

Also it's better in the long run (clearer...and possibly to avoid obselete code with an upgrade in the future) to use the INNER JOIN syntax rather than "select * from a,b" style coding.

As to your main issue, I can't spot the direct error in your proc, but you could make this resultset a derived table and do a "select distinct Inforce.[Policy#] from (SELECT Inforce.[Policy#], Inforce.InsuredName, Inforce.Status, Inforce.Productline, Inforce.[Plan], Inforce.writeagent, Inforce.[Ef Date] effdate, Agent.[Agent#], Agent.AgentName
FROM Agent, Inforce, etc.....)" type query....and bypass the specific problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources