Welcome :Guest
 Sign In Register
Win Surprise Gifts!!!
Congratulations!!!

Top 5 Contributors of the Month
ffttt
 Post New Web Links

## 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, SWITCH...CASE STATEMENT   Hello, i need help changing the following code into a switch...case statement<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)      {```

### Case statement is never executing

Can some please advise as to why the case statement is never running

```Imports Microsoft.VisualBasic
Imports AjaxControlToolkit

Public Class MultilevelAccordion
Inherits System.Web.UI.UserControl
Implements System.Web.UI.ITemplate

Dim templateType As AccordionItemType

Sub New(ByVal type As AccordionItemType)
templateType = type
MsgBox(templateType.ToString)

End Sub

Sub New()

End Sub

Public Sub InstantiateIn(ByVal container As System.Web.UI.Control) _
Implements System.Web.UI.ITemplate.InstantiateIn

Dim ph As New PlaceHolder()

Select Case (templateType)
Case AccordionItemType.Header
Dim myLabel As Label = New Label()
myLabel.ID = "HeaderLabel"
MsgBox("header")

ph.Controls.Add(myLabel)
AddHandler ph.DataBinding, New EventHandler(AddressOf Item_DataBinding)
Case AccordionItemType.Content
AddHandler ph.DataBinding, New EventHandler(AddressOf Item_DataBinding)
End Select
container.Controls.Add(ph)
End Sub

Shared Sub Item_DataBinding(ByVal sender As Object, ByVal e As AccordionItemEventArgs)

Dim ph As PlaceHolder = CType(sender, PlaceHolder)
Dim headerValue As String = _
```

### LINQ to SQL and Case statement

Working with LINQ I realized that I had to use the simple case statement in my SQL query. There is no special keyword for this. To create a case statement like structure you will have to do it in the select section of the query. Below is an example of the usage of the case statement in LINQ. var t = from n in idc.categories            select new             {                        catName =                         (n.id==1 ? "Cat1" :                        n.id==2 ? "Cat2" :                        n.id==3 ? "Cat3" : "Unknown Category"                        )             }; Here in the above code we are using multiple cases for value 1, 2 and 3

### INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

### Two Output Columns from One CASE Statement

I have some query logic that needs to return two dynamically created columns based on two conditional tests so that - If Test1 is True then the values for columns 1 and 2 are always the same regardless of the results of Test2. - If Test1 is False then Test2 determines the value of Column2 Because the CASE statement only produces one column I am having to repeat Test1 and then nest Test2 in the ELSE portion of the second iteration of Test1.  I was hoping that someone might have a different approach that would reduce the amount of testing being performed in the SQL.  I did see the post at http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a0e45c7d-eedd-41ca-89f9-163c4dadc1a7/ that doesn't offer much hope but I thought I'd ask. For what it's worth I'm posting the heart of my logic below.  The test is performing bitwise comparisons to determine (Test1:) If the currently logged in user has access to a document and, if not (Test2) what they need to do to gain access.  @Access will end up as a parameter in the stored procedure once I finalize this. DECLARE   @Access tinyint = 1 SELECT   Holding.HoldingID, ShortTitle, Access,   CASE     WHEN @Access &[Access] = @Access THEN (URL + [FileName])     ELSE null   END AS [HoldingURL],         CASE

### Oracle dataset CASE statement

Hi,I'm trying to create a dataset linking to Oracle, but I'm having problems getting a case statement to work in the query.It works fine in SQLPlus, Sql developer.When I try and add the statement it either puts single quotes around the statement, or throws the error 'Invalid or missing expression'.Here is the query in question:SELECT DISTINCT (CASE WHEN DEPARTMENT.DESCRIPTION LIKE '%Full Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Full Time') WHEN DEPARTMENT.DESCRIPTION LIKE '%Part Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Part Time') ELSE DEPARTMENT.DESCRIPTION END) AS DEPARTMENT FROM COURSE, COURSE_INSTANCE, COURSE_LEVEL, DEPARTMENT WHERE COURSE.OBJECT_ID = COURSE_INSTANCE.COURSE AND COURSE.COURSE_LEVEL = COURSE_LEVEL.OBJECT_ID AND COURSE.DEPARTMENT = DEPARTMENT.OBJECT_ID

### Pass the entire where-clause of a SQL statement through the URL?

I need to pass a ridiculous number of where-clause parameters to a SQLquery. I'd like to build a long where-clause on the client side andpass it to the web form through the URL. Is possible to pass the entire where-clause of a SQL statement to anasp.net SelectCommand instead of multiple individual field parameter?if yes, now? ---------- working------------------------------------------------------- http://localhost:1532/Default.aspx?myage=99 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [tblMain] WHERE ([age] <= @age)"> <SelectParameters> <asp:QueryStringParameter DefaultValue="0" Name="age" QueryStringField="myage" Type="String" /> </SelectParameters> </asp:SqlDataSource> ----------end working-------------------------------------------   ----------start not-working------------------------------------- http://localhost:1532/Default.aspx?para='(age<99)' <asp:SqlDataSource ID="SqlDataSource1" runat="server" Conne

### How to use switch - case statement in T-SQL..?

Hi, I want to use switch - case statement in T-SQLÃÂ stored procedure. Can any one help regarding the same..? ÃÂ  for e.g. switch (exp) { case 1 : stmt 1; break; case 2 : stmt 2; break; case 3 : stmt 3; break; & so on....... }

### Case Statement Evaluating 2 Conditions

Hello, I have a field A and B. I want A to be filterered based a condition  When B= 2 then  A should not be in (1,2,3,4) When B=1 then give me back all of A (no filters) I am trying to do something like: Case when B=2 then A Not in (1,2,3,4) else A How do I incorporate this in my case statement? Or do I need some other way to do this? Please help. Thanks to all in advance! KJ

### CASE statement

Hi all, I have quite a few of the below in my query (not the same case statement but similar): CASE WHEN [funcyion_key] <> 2 THEN SUM(portfolio_amt]) ELSE 0 END as portfolio_amt Is there a better way of doing the above? please help

### add more than one case in select statement ?

hi all

i have this select statement

select

case when X  = 1 THEN ''

ELSE

CASE WHEN y = 1 THEN 'Y' END

CASE WHEN f = e THEN 'ok' END

END

FROM table

but

Incorrect syntax near the keyword 'CASE' the secound one

how can i solve this ?

### Problem with CASE in IN-Statement

Hello everybody,

i try to use a case-statement in the IN-Statement of my WHERE-Statement. The problem is, that my statement doesn't work when i want to return two values. When I return one value it works correctly. Here my code:

```WHERE [CATEGORY] = @category and
[CONSOLVIEW] IN (
case
-- Wenn EUR_SEM geliefert wird, wird nur EUR_SEM storniert
when @consolview = 'EUR_SEM' then
'EUR_SEM'
-- Wenn LC geliefert wird und Entities mit einer anderen Währung als EUR geliefert wird
when (@consolview = 'LC' AND (@currency <> 'EUR')) then
'LC'
-- Wenn LC geliefert wird und nur EUR-Entities vorhanden sind.
else
'''' + 'LC' + ''''+', '+ '''' + 'EUR_SEM' + ''''

end )```

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