.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

Using a CASE in a WHERE clause with an IN

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server
 

Confusing title, I know. I haven't found this particular question asked/answered anywhere. I'd appreciate some help.

Depending on the state of a checkbox that says "Include Inactives," I want to select a group of customers based on their statuses. (The table has rows with statuses of A, I, C, H, and X.)

The select statement was originally written like this:

Select custNum, custName from Customer where custStatus in (case when @chkboxValue=0 then 'A' else 'I,A' end)

I don't see how, but if the Include Inactives checkbox is unchecked, it actually works, returning all customers with status of A. But if the checkbox is unchecked, I get nothing, which is what I expected because of the incorrect syntax that results from the else.

I've tried putting in lots of combinations of double apostrophes. I think this ought to work, but it doesn't:

Select custNum, custName from Customer where custStatus in (case when @chkboxValue=0 then ''A'' else ''I','A'' end)

In fact, that way, it doesn't return anything, regardless of checkbox state.

Thanks for any ideas.




View Complete Post


More Related Resource Links

Can we use Select clause in Case statement

  
SELECT CASE OT.ItemTypeID WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8 WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9 WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10 WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18 WHEN 19 THEN SELECT SUM(ISNULL(&qu

Query help to write case statement in Where clause?

  
Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

Case statement in where clause

  
i trying following querySelect  FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email,  CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID  From ClientDetail with (nolock)  Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And  if(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted')) ELSE  (ClientStatus Is Null OR Lower(ClientStatus) = 'active') )  Order By ClientDetail.CreationDate DESC ;Select FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email, CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID From ClientDetail with (nolock) Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And case(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted'))ELSE (ClientStatus Is Null OR Lower(ClientStatus) = 'active') ) Order By ClientDetail.CreationDate DESC ;but is gives an error plz help

Using CASE statement in "IN" clause of query

  

I have a sql datasource with the ControlParameter called ddlDropDownList.

2 questions:

1. Is my syntax below ok, I am getting a runttime error that the syntax is incorrect.

2. I have the select statement set up the control parameter to get the selected value from the dropdownlist as shown below, is that syntax correct?  especially the "selectedItem.value" for the propertyname property. thx


select fieldOne, fieldTwo from someTable where fieldOne IN 
CASE WHEN @ddlDropDownList='valueABC' THEN ('ABC') WHEN @ddlDropDownList='valueDEF' THEN ('DEF') 
ELSE ('ABC','DEF') END 

<asp:ControlParameter ControlID="ddlDropDownList" Name="typeSlctd" PropertyName="SelectedItem.Value" Type="String" />



Case Statement With IN Clause

  

Hi all,

I am using a query and have isssue in doing multi select with an amount field.

Actually I want to have a parameter like this:

Select ANY Amount range (from the 3 shown):-

> 8500
< -8500
BETWEEN -8500 AND 8500


The query is simple with case statement to run for either one of the range selected.
But now I want to also run by multi selection on Ranges with an OR statement.

I am able to run for either of the range selected but I am not able to run for more than one range selected.


Any help on this.

Here is my sample code.

 

create table dbo.test
(
year int,
month int,
Name varchar(12),
amount int)

INSERT INTO test ( year,month,

Re: Using CASE...WHEN inside a WHERE Clause

  

Hi,

I am trying to get some records using a SELECT statement. In the table structure I have 6 columns. Below is my table structure,

1. Employee ID  INT

2. RequestedFrom DATETIME

3. RequestedTo DATETIME

4. ApprovedFrom DATETIME

5. ApprovedTo DATETIME

6. NoOfDays INT


What I want to do is select all records from the table WHERE the [ApprovedFrom] BETWEEN @StartDate AND @EndDate, AND IF ApprovedFrom IS NULL THEN Instead of [ApprovedFrom], it must check the [RequestedFrom].


Thanks


update using case clause

  

SQL Server 2008. I need to do following. I have a table Product (productid, categoryid,unitprice). Write an UPDATE statement that will change unitprice: increase by 10 for  categoryid = 1, increase

IN clause within CASE WHEN

  

Whenever I run this query below, I always get 'NO' result, instead of the expected 'YES'.  Perhaps you cannot use the IN clause within a CASE WHEN contruct.  I'm open to suggestions.  

Thank You,

DECLARE @OPTION1 AS VARCHAR(14)
DECLARE @OPTION2 AS VARCHAR(14)
DECLARE @OPTION3 AS VARCHAR(14)
DECLARE @SEARCH AS VARCHAR(10)
DECLARE @ALL AS VARCHAR(28)

SET @OPTION1 = 'THIS'
SET @OPTION2 = 'THAT'
SET @OPTION3 = 'OTHER'

SET @SEARCH = 'OTHER'
SET @ALL =  @OPTION1 + ',' + @OPTION2 + ',' + @OPTION3

SELECT CASE WHEN @SEARCH IN ( @ALL ) THEN 'YES' ELSE 'NO' END

 


CASE STATEMENT IN WHERE CLAUSE

  

IN MY WHERE CLAUSE, I NEED TO DETERMINE THE FOLLOWING:

(((WHERE
  gl.CashierDate >= @BeginDate AND
  gl.CashierDate < DateAdd(d, 1, @EndDate) AND
 
  IF THE ACCOUNTNUMBER IS '22030000' THEN I NEED TO EXCLUDE THESE TWO  DISBURSEMENTCODE ('670','670A')
  IF THE ACCOUNTNUMBER IS '20350000' THEN I NEED TO EXCLUDE THESE FOUR DISBURSEMENTCODE ('215','215B','305','305A')

  IF THE ACCOUNTNUMBER IS ANYTHING BUT THESE TWO ACCOUNTS, THEN I DONT NEED TO EXCLUDE ANYTHING

Thank you for your help!

 

 

  I WANT TO USE CASE STATEMENT FOR THIS BUT NOT CERTAIN HOW TO DO IT.


Visual Case Tool - UML Tutorial

  
The Class Diagram

The class diagram is core to object-oriented design. It describes the types of objects in the system and the static relationships between them.

Use Case Diagram

  
The use case diagram shows the functionality of the system from an outside-in viewpoint.

Actors (stick men) are anything outside the system that interacts with the system.

Power of using Clause in SharePoint

  
The Using Clause will help to avoid memory leaks in SharePoint by automatically Disposing the MOSS objects.

For example when we create SPSite and SPWeb objects, if we don't dispose it explicitly it might create memory leaks,

SWITCH...CASE STATEMENT

  

Hello, i need help changing the following code into a switch...case statementUndecided

<script runat="server">
void Page_Load()
{
 
    if (Page.IsPostBack)
    {
    public String toSring()
    {
    int intcomputerChoice=1;
        
         if(computerChoice==1)
         {
         return(Rock);
       }
       if(computerChoice==2)
         {
         return(Paper);
       }
       if(computerChoice==3)
         {
         return(Scissors);
       }
       }
       if (Page.IsPostBack)
      {

Editor's Note: In Case You Hadn't Noticed .

  

Find out what's new for MSDN Magazine, including a print redesign and the introduction of virtual labs on our web site so you can experiment with our code.

Howard Dierking

MSDN Magazine June 2008


.NET Migration Case Study: Using ASP.NET to Build the beta.visualstudio.net Web Site

  

When the Web site used to collect customer feedback about the Visual Studio .NET beta needed an update, the Visual Studio team saw a good opportunity to implement, deploy, and showcase a real-world site using .NET technologies. This article covers the migration of the Web site, beta.visualstudio.net, from components written in Visual Basic 6.0 and ASP hosted on Microsoft Internet Information Services (IIS) 5.0 to ASP.NET and the .NET Framework. The choices of technologies to be incorporated, along with issues such as validation, security, and authentication, are all discussed. Both existing components and the new components written in JScript and ASP.NET that were used to migrate the site are also explained.

Jay Schmelzer

MSDN Magazine September 2001


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend