SQL Help Needed - 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

Transact-SQL (2000)

SQL Help Needed

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2005-09-28 : 02:55:54

I have created one procedure with one select, which used the Order By clause with case structure.
I will pass the column to order by clause on which i want sorting.

My Procedure is as below:

create proc test
(

@sortfield char(30)=NULL

)
As
Begin
declare @orderby numeric

if(ltrim(rtrim(@sortfield))= 'PayerPricingName')
select @orderby =1
else if (ltrim(rtrim(@sortfield))= 'RuleName')
select @orderby =2
else if (ltrim(rtrim(@sortfield))= 'Effective')
select @orderby =3
else if (ltrim(rtrim(@sortfield))= 'Termination')
select @orderby =4

select distinct PayerPricing.PayerPricingKey,
PayerPricing.PayerPricingName,
PayerPricing.RuleName,
PayerPricing.Effective,
PayerPricing.Termination
from PayerPricing PayerPricing ,TermRule,
ContractTerms,LookCCntrct,
PayerContracts,PayerProfile
where PayerPricing.Effective = (select max(PayerPricing1.Effective)
from PayerPricing PayerPricing1
where PayerPricing1.PayerPricingName = PayerPricing.PayerPricingName) and
PayerPricing.RuleName = TermRule.RuleName and
TermRule.TermKey = ContractTerms.TermKey and
ContractTerms.ContractID= LookCCntrct.ContractID and
PayerContracts.ContractID = LookCCntrct.ContractID and
PayerContracts.PUID = PayerProfile.PUID and
ORDER BY CASE
WHEN @orderby= 1 THEN PayerPricingName
WHEN @orderby= 2 THEN RuleName
WHEN @orderby = 3 THEN convert(varchar,Effective,120)
WHEN @orderby = 4 THEN convert(varchar,Termination,120)
else PayerPricingName
end

return
end

But this above Procedure gives me one error like :

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Can anyone help me out for the query? Thank you.

shubhada

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 03:07:34

If you use Distinct and Order by Clause then all the columns that appear in Order by Should be selected. In your select query include
convert(varchar,Effective,120) and convert(varchar,Termination,120)
and see if that works

Madhivanan

Failing to plan is Planning to fail

shubhada
Posting Yak Master

117 Posts

Posted - 2005-09-28 : 04:48:48

I have tried order by clause without convert function.

select distinct PayerPricing.PayerPricingKey,
PayerPricing.PayerPricingName,
PayerPricing.RuleName,
PayerPricing.Effective,
PayerPricing.Termination
from PayerPricing PayerPricing ,TermRule,
ContractTerms,LookCCntrct,
PayerContracts,PayerProfile
where PayerPricing.Effective = (select max(PayerPricing1.Effective)
from PayerPricing PayerPricing1
where PayerPricing1.PayerPricingName = PayerPricing.PayerPricingName) and
PayerPricing.RuleName = TermRule.RuleName and
TermRule.TermKey = ContractTerms.TermKey and
ContractTerms.ContractID= LookCCntrct.ContractID and
PayerContracts.ContractID = LookCCntrct.ContractID and
PayerContracts.PUID = PayerProfile.PUID and
ORDER BY CASE
WHEN @orderby= 1 THEN PayerPricingName
WHEN @orderby= 2 THEN RuleName
WHEN @orderby = 3 THEN Effective
WHEN @orderby = 4 THEN Termination
else PayerPricingName
end

but still i got same error.

Server: Msg 145, Level 15, State 1, Procedure test, Line 81
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

shubhada

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-28 : 05:17:31

you're actually joining tables, be sure to specify the source of the fields in your group by

HTH

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-28 : 05:55:05

Try this

Select * from 
		(
		select distinct PayerPricing.PayerPricingKey,
			PayerPricing.PayerPricingName,
			PayerPricing.RuleName,
			PayerPricing.Effective,
			PayerPricing.Termination 
		from PayerPricing PayerPricing ,TermRule,
			ContractTerms,LookCCntrct,
			PayerContracts,PayerProfile
		where PayerPricing.Effective = 
			(select max(PayerPricing1.Effective)
			from PayerPricing PayerPricing1
			where PayerPricing1.PayerPricingName = PayerPricing.PayerPricingName) and
			PayerPricing.RuleName = TermRule.RuleName and
			TermRule.TermKey = ContractTerms.TermKey and
			ContractTerms.ContractID= LookCCntrct.ContractID and
			PayerContracts.ContractID = LookCCntrct.ContractID and
			PayerContracts.PUID = PayerProfile.PUID
		) T
ORDER BY CASE 
WHEN @orderby= 1 THEN PayerPricingName 
WHEN @orderby= 2 THEN RuleName 
WHEN @orderby = 3 THEN Effective
WHEN @orderby = 4 THEN Termination
else PayerPricingName 
end

Madhivanan

Failing to plan is Planning to fail

shubhada
Posting Yak Master

117 Posts

Posted - 2005-09-30 : 07:26:37

hi Madhivanan
it is working fine...
thanks a lot.

shubhada

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources