Post New Web Links

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

Posted By: Posted Date: October 27, 2010 Points: 0 Category :ASP.Net

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

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

how to use case ..when inside a case... when

SELECT A= CASE 1
                       WHEN 0 THEN 0
                           ELSE
                           //---here caused error
                               CASE WHEN -1<0
                                       THEN '-1<0'
                                   ELSE
                                       THEN '-1>0'
                             &nbs

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" />

Using a CASE in a WHERE clause with an IN

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.

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, 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.
 Accessing the different controls inside a GridView control 
  how we can access a particular control which resides inside a GridView control. In this article I will show you how you can access different controls inside a GridView control. We will see how we can access a TextBox control, a DropDownList control and a ListBox control. If you are working with ASP.NET 1.X then you might want to check out my article Accessing Different Controls Inside a DataGrid.
 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.
 How To Add calendar control inside a GridView 
  This article show hot to add calendar control inside a GridView with code in c#
 Using ATLAS PopUp Control with Calendar inside the GridView Control 
  In this video tutorial Mohammad demonstrates how you can access the Calendar control which is displayed using the ATLAS PopUp control inside the GridView control.
 RadioButton inside GridView, How to get it work as normal 
  Did you tried before to drag a RadioButton control inside a Gridivew templatefield, and then you attempt to select these RadioButtons , you will notice that the behavior of RadioButton control will be changed and it will work just like the behavior of checkbox control! the user will be able to select more than one radiobutton in the grid!
 Maintain HTML Radio Button Selection Inside GridView After Postback 
  Long time ago I wrote an article about getting the value of the selected RadioButtons inside the GridView control. The code did not addresssed the postback issue which means that the selected radio button was cleared after the postback occurs.


Categories:

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