Conditional Order By - 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)

Conditional Order By

Author

Topic

elefantmannen
Starting Member

2 Posts

Posted - 2006-02-10 : 06:02:53

Hello,
Im trying to use case within a group by clause. What I want is to use the having clause if a parameter (@stepid) is null, otherwise I just want to group without the having clause. I have tried just about everything, the code belove is what I think should work, but it doesnt :(

Problem here seems to be the ',' and the '='. Apparently it doesnt want these characters in the case clause.

any suggestions?

like this:

GROUP BY CASE isnull(@stepid,1) WHEN 1 THEN
rpStep.StepId, St.parentStepId having
((SELECT dbo.fStepIsTop(rpStep.StepID, St.parentStepId)) = 1)
ELSE
rpStep.StepId, St.parentStepId)
END

Kind regards
Elefantmannen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-10 : 07:54:23

GROUP BY CASE when isnull(@stepid,1) WHEN 1 THEN
rpStep.StepId else St.parentStepId end
having

I've no idea what you are trying to do with this
The group by an having clauses are self contained - you cannot have a case statement starting in the group by and ending in the having (what would it mean anyway).

((SELECT dbo.fStepIsTop(rpStep.StepID, St.parentStepId)) = 1)
ELSE
rpStep.StepId, St.parentStepId)
END

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elefantmannen
Starting Member

2 Posts

Posted - 2006-02-10 : 08:05:49

I am not trying to end it within the having clause. What I want to do is Group by the same columns but use the having clause if the in-parameter @stepid is null.

GROUP BY CASE isnull(@stepid,1) WHEN 1 THEN
--if @strepid is null then use the having clause
(rpStep.StepId, St.parentStepId having
((SELECT dbo.fStepIsTop(rpStep.StepID, St.parentStepId)) = 1))
ELSE
--else do not use the having clause
rpStep.StepId, St.parentStepId
END

kind regards
elefantmannen

Jothikannan
Starting Member

36 Posts

Posted - 2006-02-10 : 08:41:11

try u'r query Like follow ..it may have chance to work

GROUP BY
rpStep.StepId, St.parentStepId
HAVING 1 = CASE isnull(@stepid,1)
WHEN 1 THEN
dbo.fStepIsTop(rpStep.StepID, St.parentStepId)
ELSE
1
END

and I donot Know why U using Case at "HAVING" Clause it Will definetly affect
u'r query performance,Y donot u change the code like follows?

COZ U KNOW THE VALUE OF @stepid WELL BEFORE

if isnull(@stepid,1) =1
SELECT fields FROM tables
GROUP BY
rpStep.StepId, St.parentStepId
HAVING dbo.fStepIsTop(rpStep.StepID, St.parentStepId) =1
else
SELECT fields FROM tables
GROUP BY
rpStep.StepId, St.parentStepId

end

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-10 : 08:42:05

Lots of things going on here....

1) Why is this in the HAVING clause, and not in the WHERE clause? If you are just grouping on those columns (StepID, ParentStepID) then you can put the condition in the WHERE clause and it should be more efficient.

however ...

2) your criteria is based on a lookup from a UDF -- that is about as inefficient as you can get. SQL must evaluate EVERY SINGLE ROW and call that UDF for each of them to determine which ones to return. In addition, doing lookups using UDF's tends to be very, very inefficient when a JOIN is preferred.

It might be best to step back, show us your SELECT and that UDF, give us some info, and help us help you to rewrite this thing properly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources