?
Solved

CASE Statement Syntax - ??? never saw befor.

Posted on 2011-10-10
4
Medium Priority
?
346 Views
Last Modified: 2012-05-12
Hi, Everyone:

I have never come accross this syntax in a CASE statement before; ... END = 1 ...
please see below.  

What does it mean?

Thank you,

      SELECT TOP (@maxIndexSize) @requestID
            , svo.MlsNum
      FROM
            mls_unified_svo_tbl svo (nolock)
      WHERE
            svo.LoadOnInternet = 1
            AND svo.std IS NOT NULL
            AND svo.snd IS NOT NULL      
            AND (svo.Status IN (5, 30, 45)
                        OR
                        (svo.Status IN (10, 20)
                              AND DATEDIFF(day, svo.StatusDate, GETDATE()) < 365
                         )
                  )
                  
            AND
                  CASE WHEN @request.exist('/searchRequest/mlsNumber') = 0
                        THEN 1
                        ELSE
                              CASE WHEN svo.MlsNum = @request.value('(/searchRequest/mlsNumber)[1]', 'varchar(20)') THEN 1
                              ELSE 0
                              END
                        END = 1

            AND
                  CASE WHEN @request.exist('/searchRequest/zipCode') = 0
                        THEN 1
                        ELSE
                              CASE WHEN svo.zip = @request.value('(/searchRequest/zipCode)[1]', 'varchar(500)') THEN 1
                              ELSE 0
                              END
                  END = 1
            
            AND
                  CASE WHEN @request.exist('/searchRequest/city') = 0
                        THEN 1
                        ELSE
                              CASE WHEN svo.city = @request.value('(/searchRequest/city)[1]', 'varchar(50)') THEN 1
                              ELSE 0
                              END
                        END = 1
0
Comment
Question by:DBA2000
  • 2
4 Comments
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 800 total points
ID: 36945650
It is because the CASE is in the WHERE clause. It needs to be a BOOLEAN expression; therefore, what it is doing is evaluating the conditions to either 0 or 1 and then comparing if you have met one of the 1 conditions. :)
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 1200 total points
ID: 36945651
The CASE END builds an expression (in this case resulting in either 0 or 1), and that expression is compared against a constant, =1. Since MSSQL does not know of boolean data type, you need to use a trick like this one to code special conditions.
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 1200 total points
ID: 36945665
It could also be coded with true conditions, like
AND  (
   @request.exist('/searchRequest/mlsNumber') = 0 or svo.MlsNum = @request.value('(/searchRequest/mlsNumber)[1]', 'varchar(20)'
) AND ...

Open in new window

0
 

Author Closing Comment

by:DBA2000
ID: 36945930
I get it!  Thank you very much.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question