case statement within SELECT ROW_NUMBER() OVER(...)

Question

Post reply

case statement within SELECT ROW_NUMBER() OVER(...)

Ham-416672

SSChasing Mays

Points: 611
More actions
December 4, 2007 at 4:04 pm

#62985

Invalid syntax near the keyword 'case'.
SELECTROW_NUMBER()
OVER (
case @sort
when 'relevance' then ORDER BY hits DESC
else ORDER BY name ASC
end
) AS row ....
I want the OVER sorting to be either ORDER BY hits DESC or ORDER BY name ASC depending on the value of @sort varchar(50), WITHOUT using dynamic queries.
How can I do something like this?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110206 More actions · Answer 1

I guess it's a wrong forum for this question.

SQL Server 7,2000 don't support such constructions.

_____________
Code for TallyGenerator

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

Though, such constructions are not supported in SQL 7, 2000, but here is what you can do in 2005

SELECTROW_NUMBER()

OVER

( ORDER BY

( CASE WHEN @sort = 'relevance' THEN hits END ) DESC,

( CASE WHEN @sort != 'relevance' THEN [name] END ) ASC

) AS row ....

--Ramesh

Ham-416672 SSChasing Mays Points: 611 More actions · Answer 3

Thanks Ramesh, works out great.

I have also been trying something like:

select * from Listings where lis_name=@lis_name

(

case when @franchise is not null then ' AND lis_franchise=@lis_franchise'

)

What I want to do is if it sees a non-null @lis_franchise parameter, I want to append " AND lis_franchise=@lis_franchise" to the WHERE clause...again without using dynamic queries. Is this also possible?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

No - not like that.

CASE is used to return single scalar values, but not to add clauses to a SQL statement (or to add to a clause).

You need dynamic SQL of some sort for that, or you need to use the case a little differently.

something like below

select *

from

Listings

where

lis_name=@lis_name

and isnull(@franchise,-999)=case when @franchise is not null then lis_franchise else -999 end

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ham-416672 SSChasing Mays Points: 611 More actions · Answer 5

Matt, I get a "Conversion failed when converting the varchar value 'some franchise' to data type int." error with your case statement. Again I like to keep the solution using static queries if at all possible.

Thanks

Ham

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

I made an assumption as to what kind of data @franchise was. if it's supposed to be some kind of string, put quotes around the -999 (both instances), which I just picked as some random "not likely to occur" value in an identity field.

in other words:

select *

from

Listings

where

lis_name=@lis_name

and isnull(@franchise,'-999')=case when @franchise is not null then lis_franchise else '-999' end

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ham-416672 SSChasing Mays Points: 611 More actions · Answer 7

Great, it worked, but can you explain to me exactly what's going on here:

isnull(@franchise,'-999')=case when @franchise is not null then lis_franchise else '-999' end

What does isnull(@franchise,'-999')= mean and what is the -999 for?

Thanks,

Ham

Sergiy SSC Guru Points: 110206 More actions · Answer 8

If to assume that "hits" is a positive integer number then this should work:

SELECT {blah}

FROM {sometable}

ORDER BY CASE WHEN @sort = '{whatever}' THEN STR(2147483647-hits, 10) ELSE name END ASC

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

ISNULL(a,b) is a function. It check to see if A is null: if it isn't - it returns A, otherwise B. I was just using it as an arbitrary value, so that when @franchise happens to be null, your where ends up essetially performing this query:

...

where

lis_name=@lis_name

and -999=-999

the last line is always true, so it's functionally the same as if it wasn't there. it's a way to "cancel" out a criteria in the WHERE statement.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 10

Although, Matt's solution do provide the results you wanted, but it uses functions in WHERE clause which can be avoided. Using functions in WHERE clauses is not always scalable in production as it forces optimizer to exclude the appropriate indexes, if any available for it.

Here is the code which would also give you the same results....

SELECT*

FROMListings

WHERElis_name = @lis_name

AND

(

( @franchise IS NULL ) OR ( lis_franchise = @franchise )

)

--Ramesh