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


Post New Web Links

Can we use Select clause in Case statement

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
 
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


View Complete Post


More Related Resource Links

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

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 ?


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,

Delete Statement with nested select in where clause deletes all entries in a table

  

I have fallen into a trap unadvertedly causing all records in a table being deleted. Can anybody tell me, if this a the intended behaviour of SQL Server 2008 R2 or does anybody know a method how to safely discover those kinds of typos in advance?

Regards, Markus

Here follows a script reproducing the behaviour:

SET

 

ANSI_NULLS ON

GO

SET

 

QUOTED_IDENTIFIER ON

GO

Use of CASE statement in Select using SqlDataSource

  

Hi All,

I need help regarding the use of CASE in Select of SQLDataSource. Actually I am using FORMVIEW and in the Grid i have "Consumer Numbers" as link and on click I save the Consumer Number in Session Variable. When user go into Edit and change the Consumer Number after updation the formview was coming blank cause i was using that number for select statement now. Just need to correct the SELECT and use the case properly and want to do is

"IF CONSUMER_NUMBER = <SESSION_VARIABLE_OF_CONSUMER_NO> THEN USE <SESSION VARIABLE>

ELSE

USE FIELD "CONSUMER NUMBER

How to do that please correct.


       <asp:SqlDataSource ID="SqlDSBillers" runat="server" 
ConnectionString="<%$ ConnectionStrings:SQLE2K8 %>"

SelectCommand="SELECT Bill_Master.Bill_Master_ID,
Bill_Master.Biller_ID,
CONVERT (varchar(20),
Bill_Master.Enroll_Date, 105) AS Enroll_Date,
Billers.Biller_Code,
Bill_Master.Consumer_No,
Bill_Master.NameOnConnection,

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.


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)
      {

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 = _

web services transactions (select statement)

  
 Hi everybody, i'm just getting started in web services and i have a problem connecting to a database. All i want is that my web service recieves a string parameter and return a dataset so that in the client aplication i just show the result of the web service in a datagrid. Here's the code of the web service:    <%@ WebService Language="C#" CodeBehind="Service1.asmx.cs" %> using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Linq;using System.Web;using System.Web.Services;using System.Web.Services.Protocols;using System.Xml.Linq;using System.EnterpriseServices;using System.Data.SqlClient;namespace Farmacia{    [WebService(Namespace = "http://localhost/MyWebServices")]    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]    [ToolboxItem(false)]    public class Farmacia : System.Web.Services.WebService    {        private SqlDataAdapter da;        [WebMethod(TransactionOption=TransactionOption.RequiresNew)]        public DataSet Consultas (string sel)        {            if (sel == "")&

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

SQL DIAGOSITC MANAGER tools capture "SELECT *" statement while ALTER / ADD CHECK CONSTRAINT is execu

  
Hello  ,   I exectuted the code(alter/add with check)  below in SSMS . My_table has 200 millions records . While running the alter/add with check query  the server crashed and the “SQL DIAGOSITC MANAGER” which run on the top of sql serever alert the DBA that select * from my_table was obtained from the last command issued by the user using a DBCC command.      Where does the select * from my_table comes from ? I didn’t excuted select * from my_table at all .   After the sql serever re started I was able to run the command below with out any problem. I checked the SQL PROFILER for the trace there is no “select * from my_table” .   So again where this select * from my_table comes from ?   Here is my initial query ----------------------------------------------------------------- ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key1] FOREIGN KEY([Key1]) REFERENCES [Dbo].[Dim_table1] ([Key1]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key1] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key2] FOREIGN KEY([key2]) REFERENCES [Dbo].[Dim_table2] ([key2]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key2] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key3] FOREIGN KEY([d], [c], [r], [g]) REFERENCES [Dbo].my

SELECT statement to return NULL by matching data from another table.

  
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

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

How do i use the select statement in functions?

  
hi friends, i have table-value function, but the has the bug in the body and i cannot debug, becuase it does not allow the select statement in it. is there away to print values to the screen, when debugging? PS: in not using a SP to debug it thanks

Assistance with SELECT statement requested

  
Hi All, I am having trouble getting started on writing a select statement to meet the following requirements.  I have a table with Employee Ids, address types, and address information.  I would like to obtain one record for each EmplID based upon the Type column; the PRF type record if it exists, otherwise then the PRI record.  (See Table A) TableA Rcd #, EmplID, Type, Address Data 1, 000001, PRI, etc 2, 000001, PRF, etc 3, 000002, PRI, etc 4, 000003, PRI, etc 5, 000003, PRF, etc 6, 000003, OTH, etc So, my result set from the table above would be: 2, 000001, PRF, etc 3, 000002, PRI, etc 5, 000003, PRF, etc What is the best way to obtain this information via a SELECT statement?  Thanks in advance, Steve  
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