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


Top 5 Contributors of the Month
Easy Web
Imran Ghani
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.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