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


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Asad Ali
Fauzul Azmi
Post New Web Links

add more than one case in select statement ?

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server
 

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 ?




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

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,

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

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  

Get output from storedprocedure and assign in Select statement

  
Is it possible to get the output from stored procedure and assign into select statement. for example. Here prc_GetComplete is another storeprocedure to get the value for @Completes INSERT INTO TABELE1 (ID, address, completes ) SELECT NEWID(), t2.address, @Completes = EXEC prc_GetComplete t2.ID , @Completes OUT FROM TABELE2 t2   Regards, teesh

ODBC Select if Statement in MAS90 query

  
Hi, I am connecting to a MAS90 database via DSN and am having trouble with the query string for ODBC.  I can collect most all fields if I SELECT them directly, however, I want to create a result column based on which cost is higher. As far as I know, this select statement in to and ODBC connection needs to comply with MSSQL I have tried SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile And SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile Neither are working?  What am I missing here? Can I do this sort of if then statement in MSSQL?   Thanks for your help 

Select statement

  
Hi All. Greetings. I have 2 tables.Table =Person ID Person ---------- ----------- 1 ali2 abuTable = DetailID Person_ID fakeID---------- ----------- --------------1 1 442 6 553 2 66select * from person, detail where person.id=detail.person_id and fakeID=55I want it show me record of 55 because of Person_ID 6 is not in Table Person and i am using operator AND,so It is not showing any thing.But i want, it show me record of 55 so what i change in this statement or which opertor i use .... so it show me.select * from person, detail where person.id=detail.person_id and fakeID=55I mean i want if value 6 in the (table Person) it show me the name of Person, but if ID 6 or value 6 not there even it show mebut show me empty place of name. Because 6 i required. ID Person ---------- ----------- 6 Any if there is 6 in the (Table Person) then ID Person ---------- ----------- 6 Rubi Thanks Advance

Textbox -> Value -> Gridview -> Select Statement

  
Hi guys. I have textbox that searches for first or last name. Result show in gridview. But when add value in text box and push my button Find nothing happen. Here code:aspx:........ <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="MobileNum" DataSourceID="SqlDataSource5" style="font-family: Verdana" onselectedindexchanged="GridView1_SelectedIndexChanged"> <Columns> <asp:BoundField DataField="FNAME" HeaderText="FNAME" SortExpression="FNAME" /> <asp:BoundField DataField="LNAME" HeaderText="LNAME" SortExpression="LNAME" /> <asp:BoundField DataField="MobileNum" HeaderText="MobileNum" ReadOnly="True" SortExpression="MobileNum" /> <asp:BoundField DataField="HomeNum" HeaderText="HomeNum" SortExpression="HomeNum" /> <asp:BoundField DataField="OfficeNum" HeaderText="OfficeNum" SortExpression="OfficeNum" /> <asp:Bo

How to read excel header name and put into SELECT statement?

  
Hi,        I'm trying to read Excel file header column name. Then I can query data from excel using SELECT statement. For instance. column name is name, department and email.  Then I want this three header name being detected using code and put in select statement. User no need to care about the header name and we can use code to read the header name instead of hardcode header name inside SELECT statement.       Please provide the details. Thanks.

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

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