Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

double case statement

I have a case statement I am trying to use to prioritize the ordering.
How can I add 2 clauses to the case?

When I do this is works:
CASE WHEN state = @stateAb THEN 0 ELSE 1 END

when I try to do this, I don't get it returned state = only
CASE WHEN state = @stateAb and usedForCity = 0 THEN 0 ELSE 1 END
0
rivkamak
Asked:
rivkamak
1 Solution
 
DultonCommented:
Try nesting the case statements.... what you're trying works in later versions of SQL, not familiar with 2005 enough to say if this was added later. the below should work though.
case when state=@stateAb THEN CASE WHEN usedForCity=0 THEN 0 ELSE 1 END ELSE 1 END

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>when I try to do this, I don't get it returned state = only
Offhand the syntax appears correct, assuming state and @stateAb does not contain a NULL value.  If there are NULLs, then you'll have to use the ISNULL function to handle them.   Can you post your full T-SQL?

In case it helps, an example of nesting CASE statements is in SQL Server CASE Solutions, do a find on 'CASE blocks can also be nested within themselves'.

>use to prioritize the ordering.
Also, the section below that deals with CASE in an ORDER BY clause.
0
 
rivkamakAuthor Commented:
select  top 100 percent * into #t1 from k4kReviewsForSite2013 ORDER BY
   -- First sort position
 CASE WHEN state = @stateAb THEN 0 ELSE 1 END
 
 select row_number() over( order by (select 1)) rn, *  into #t2
from #T1 

--select * from #t1
--select * from #t2

select * from #T2 where rn between @startNum and @endNum --order by idforstate

Open in new window


when I write to the temporary table  it works the first way.
when when I add the second clause, i lose the correct ordering.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'the correct ordering'.
0
 
rivkamakAuthor Commented:
right ordering is only if state still shows up first.
0
 
awking00Commented:
Perhaps you could post some sample data and your expected results to help clarify your intent for us?
0
 
PaulCommented:
It may depend on nulls in the usedForCity column, try these

-- if usedForCity is null send it downward in ordering
CASE WHEN state = @stateAb and ISNULL(usedForCity,1) = 0 THEN 0 ELSE 1 END

or

-- if usedForCity is null send it upward in ordering
CASE WHEN state = @stateAb and ISNULL(usedForCity,0) = 0 THEN 0 ELSE 1 END


If state can be null also then more may be required, e.g.

-- if usedForCity is null send it downward in ordering
CASE WHEN ISNULL(state,'') = @stateAb and ISNULL(usedForCity,1) = 0 THEN 0 ELSE 1 END

Sample data and expected results would help us help you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now