Professional Web Applications Themes

Case statement help - Microsoft SQL / MS SQL Server

Currently I have a Case statement in an update statement that looks like this: update #MeetingsTmp set MeetingCount = (select count(*) from meeting m where m.companyid = mt.companyid and m.MeetingDate = mt.MeetingDate and MeetingTypeID = (CASE mt.MeetingType WHEN 'A' THEN 0 WHEN 'C' THEN 4 WHEN 'S' THEN 3 WHEN 'U' THEN 6 WHEN 'R' THEN 0 WHEN 'T' THEN 3 WHEN 'V' THEN 3 WHEN 'W' THEN 3 WHEN 'X' THEN 3 WHEN 'Y' THEN 3 WHEN 'Z' THEN 1 WHEN 'P' THEN 3 WHEN 'S' THEN 3 WHEN 'Q' THEN 3 ELSE convert(tinyint, MeetingType) END) and MeetingStatusID NOT ...

  1. #1

    Default Case statement help

    Currently I have a Case statement in an update statement that looks like
    this:

    update #MeetingsTmp set MeetingCount =
    (select count(*) from meeting m
    where m.companyid = mt.companyid
    and m.MeetingDate = mt.MeetingDate
    and MeetingTypeID = (CASE mt.MeetingType WHEN 'A' THEN 0
    WHEN 'C' THEN 4
    WHEN 'S' THEN 3
    WHEN 'U' THEN 6
    WHEN 'R' THEN 0
    WHEN 'T' THEN 3
    WHEN 'V' THEN 3
    WHEN 'W' THEN 3
    WHEN 'X' THEN 3
    WHEN 'Y' THEN 3
    WHEN 'Z' THEN 1
    WHEN 'P' THEN 3
    WHEN 'S' THEN 3
    WHEN 'Q' THEN 3
    ELSE convert(tinyint, MeetingType) END)
    and MeetingStatusID NOT IN (0,1,2,3,8,9) and ConfirmedYN = 1)
    from #MeetingsTmp mt with (NOLOCK)
    where MeetingCount > 1

    I want to know it there a way to continue to use a case statement but say
    something like:
    WHEN 'X' THEN 3
    WHEN 'X' THEN 2
    Basically one value in the WHEN clause goes to 2 values in the THEN
    statement.

    I appreciate any help.
    thanks,
    Meenal




    Meenal Guest

  2. #2

    Default Re: Case statement help

    Why would you want to do this? What would make you choose a 3 or a 2?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:phx.gbl...
    Currently I have a Case statement in an update statement that looks like
    this:

    update #MeetingsTmp set MeetingCount =
    (select count(*) from meeting m
    where m.companyid = mt.companyid
    and m.MeetingDate = mt.MeetingDate
    and MeetingTypeID = (CASE mt.MeetingType WHEN 'A' THEN 0
    WHEN 'C' THEN 4
    WHEN 'S' THEN 3
    WHEN 'U' THEN 6
    WHEN 'R' THEN 0
    WHEN 'T' THEN 3
    WHEN 'V' THEN 3
    WHEN 'W' THEN 3
    WHEN 'X' THEN 3
    WHEN 'Y' THEN 3
    WHEN 'Z' THEN 1
    WHEN 'P' THEN 3
    WHEN 'S' THEN 3
    WHEN 'Q' THEN 3
    ELSE convert(tinyint, MeetingType) END)
    and MeetingStatusID NOT IN (0,1,2,3,8,9) and ConfirmedYN = 1)
    from #MeetingsTmp mt with (NOLOCK)
    where MeetingCount > 1

    I want to know it there a way to continue to use a case statement but say
    something like:
    WHEN 'X' THEN 3
    WHEN 'X' THEN 2
    Basically one value in the WHEN clause goes to 2 values in the THEN
    statement.

    I appreciate any help.
    thanks,
    Meenal





    Tom Guest

  3. #3

    Default Re: Case statement help

    > I want to know it there a way to continue to use a case statement but say 

    I don't think you'll be able to use = to resolve what you want. You will
    likely have to use an in clause, e.g. IN (2, 3)

    Why do you have MeetingType CHAR(1) and MeetingTypeID INT? Couldn't you
    make it a little more relational by just storing the meetingTypeID? Have a
    view display the character representation if you really need to do that...


    Aaron Guest

  4. #4

    Default Re: Case statement help

    The meetingtype in #MeetingsTmp could say X but the MeetingTypeID which is coming form the Meeting table could be either a 2 or 3 and both would be acceptable values for the update statement which is trying to get a count. Hope I'm explaining myself well.

    thanks
    Meenal
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:#phx.gbl...
    Why would you want to do this? What would make you choose a 3 or a 2?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:phx.gbl...
    Currently I have a Case statement in an update statement that looks like
    this:

    update #MeetingsTmp set MeetingCount =
    (select count(*) from meeting m
    where m.companyid = mt.companyid
    and m.MeetingDate = mt.MeetingDate
    and MeetingTypeID = (CASE mt.MeetingType WHEN 'A' THEN 0
    WHEN 'C' THEN 4
    WHEN 'S' THEN 3
    WHEN 'U' THEN 6
    WHEN 'R' THEN 0
    WHEN 'T' THEN 3
    WHEN 'V' THEN 3
    WHEN 'W' THEN 3
    WHEN 'X' THEN 3
    WHEN 'Y' THEN 3
    WHEN 'Z' THEN 1
    WHEN 'P' THEN 3
    WHEN 'S' THEN 3
    WHEN 'Q' THEN 3
    ELSE convert(tinyint, MeetingType) END)
    and MeetingStatusID NOT IN (0,1,2,3,8,9) and ConfirmedYN = 1)
    from #MeetingsTmp mt with (NOLOCK)
    where MeetingCount > 1

    I want to know it there a way to continue to use a case statement but say
    something like:
    WHEN 'X' THEN 3
    WHEN 'X' THEN 2
    Basically one value in the WHEN clause goes to 2 values in the THEN
    statement.

    I appreciate any help.
    thanks,
    Meenal




    Meenal Guest

  5. #5

    Default Re: Case statement help

    Agreed... but the value for MeetingType CHAR(1) is coming from an outside
    source and even if I tried to translate it to MeetingTypeID right of the bat
    to store only the MeetingTypeID I would have the same issue where
    MeetingType X could match up to either MeetingTypeID 2 or 3.
    Will try a view.

    thanks,
    Meenal


    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... [/ref]
    say 
    >
    > I don't think you'll be able to use = to resolve what you want. You will
    > likely have to use an in clause, e.g. IN (2, 3)
    >
    > Why do you have MeetingType CHAR(1) and MeetingTypeID INT? Couldn't you
    > make it a little more relational by just storing the meetingTypeID? Have[/ref]



    Meenal Guest

  6. #6

    Default Re: Case statement help

    I guess I don't follow. If either 3 or 2 is acceptable, then just pick one of them yourself and use it.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:#phx.gbl...
    The meetingtype in #MeetingsTmp could say X but the MeetingTypeID which is coming form the Meeting table could be either a 2 or 3 and both would be acceptable values for the update statement which is trying to get a count. Hope I'm explaining myself well.

    thanks
    Meenal
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:#phx.gbl...
    Why would you want to do this? What would make you choose a 3 or a 2?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:phx.gbl...
    Currently I have a Case statement in an update statement that looks like
    this:

    update #MeetingsTmp set MeetingCount =
    (select count(*) from meeting m
    where m.companyid = mt.companyid
    and m.MeetingDate = mt.MeetingDate
    and MeetingTypeID = (CASE mt.MeetingType WHEN 'A' THEN 0
    WHEN 'C' THEN 4
    WHEN 'S' THEN 3
    WHEN 'U' THEN 6
    WHEN 'R' THEN 0
    WHEN 'T' THEN 3
    WHEN 'V' THEN 3
    WHEN 'W' THEN 3
    WHEN 'X' THEN 3
    WHEN 'Y' THEN 3
    WHEN 'Z' THEN 1
    WHEN 'P' THEN 3
    WHEN 'S' THEN 3
    WHEN 'Q' THEN 3
    ELSE convert(tinyint, MeetingType) END)
    and MeetingStatusID NOT IN (0,1,2,3,8,9) and ConfirmedYN = 1)
    from #MeetingsTmp mt with (NOLOCK)
    where MeetingCount > 1

    I want to know it there a way to continue to use a case statement but say
    something like:
    WHEN 'X' THEN 3
    WHEN 'X' THEN 2
    Basically one value in the WHEN clause goes to 2 values in the THEN
    statement.

    I appreciate any help.
    thanks,
    Meenal





    Tom Guest

  7. #7

    Default Re: Case statement help

    I do not want to select the meetingtypeid just yet since I first need to determine if there are multiple meetings of the same type on that day in our system.
    It is a little long and drawn out but I first need to get the count of how many meeting of the same kind are occurring on the same day before proceeding.

    thanks
    Meenal
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:#phx.gbl...
    I guess I don't follow. If either 3 or 2 is acceptable, then just pick one of them yourself and use it.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:#phx.gbl...
    The meetingtype in #MeetingsTmp could say X but the MeetingTypeID which is coming form the Meeting table could be either a 2 or 3 and both would be acceptable values for the update statement which is trying to get a count. Hope I'm explaining myself well.

    thanks
    Meenal
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:#phx.gbl...
    Why would you want to do this? What would make you choose a 3 or a 2?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:phx.gbl...
    Currently I have a Case statement in an update statement that looks like
    this:

    update #MeetingsTmp set MeetingCount =
    (select count(*) from meeting m
    where m.companyid = mt.companyid
    and m.MeetingDate = mt.MeetingDate
    and MeetingTypeID = (CASE mt.MeetingType WHEN 'A' THEN 0
    WHEN 'C' THEN 4
    WHEN 'S' THEN 3
    WHEN 'U' THEN 6
    WHEN 'R' THEN 0
    WHEN 'T' THEN 3
    WHEN 'V' THEN 3
    WHEN 'W' THEN 3
    WHEN 'X' THEN 3
    WHEN 'Y' THEN 3
    WHEN 'Z' THEN 1
    WHEN 'P' THEN 3
    WHEN 'S' THEN 3
    WHEN 'Q' THEN 3
    ELSE convert(tinyint, MeetingType) END)
    and MeetingStatusID NOT IN (0,1,2,3,8,9) and ConfirmedYN = 1)
    from #MeetingsTmp mt with (NOLOCK)
    where MeetingCount > 1

    I want to know it there a way to continue to use a case statement but say
    something like:
    WHEN 'X' THEN 3
    WHEN 'X' THEN 2
    Basically one value in the WHEN clause goes to 2 values in the THEN
    statement.

    I appreciate any help.
    thanks,
    Meenal




    Meenal Guest

  8. #8

    Default Re: Case statement help

    You can probably do a correlated subquery to get the number. If you can post a simple repro script, we may be able to help.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:phx.gbl...
    I do not want to select the meetingtypeid just yet since I first need to determine if there are multiple meetings of the same type on that day in our system.
    It is a little long and drawn out but I first need to get the count of how many meeting of the same kind are occurring on the same day before proceeding.

    thanks
    Meenal
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:#phx.gbl...
    I guess I don't follow. If either 3 or 2 is acceptable, then just pick one of them yourself and use it.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:#phx.gbl...
    The meetingtype in #MeetingsTmp could say X but the MeetingTypeID which is coming form the Meeting table could be either a 2 or 3 and both would be acceptable values for the update statement which is trying to get a count. Hope I'm explaining myself well.

    thanks
    Meenal
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:#phx.gbl...
    Why would you want to do this? What would make you choose a 3 or a 2?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Meenal Dhody" <com> wrote in message news:phx.gbl...
    Currently I have a Case statement in an update statement that looks like
    this:

    update #MeetingsTmp set MeetingCount =
    (select count(*) from meeting m
    where m.companyid = mt.companyid
    and m.MeetingDate = mt.MeetingDate
    and MeetingTypeID = (CASE mt.MeetingType WHEN 'A' THEN 0
    WHEN 'C' THEN 4
    WHEN 'S' THEN 3
    WHEN 'U' THEN 6
    WHEN 'R' THEN 0
    WHEN 'T' THEN 3
    WHEN 'V' THEN 3
    WHEN 'W' THEN 3
    WHEN 'X' THEN 3
    WHEN 'Y' THEN 3
    WHEN 'Z' THEN 1
    WHEN 'P' THEN 3
    WHEN 'S' THEN 3
    WHEN 'Q' THEN 3
    ELSE convert(tinyint, MeetingType) END)
    and MeetingStatusID NOT IN (0,1,2,3,8,9) and ConfirmedYN = 1)
    from #MeetingsTmp mt with (NOLOCK)
    where MeetingCount > 1

    I want to know it there a way to continue to use a case statement but say
    something like:
    WHEN 'X' THEN 3
    WHEN 'X' THEN 2
    Basically one value in the WHEN clause goes to 2 values in the THEN
    statement.

    I appreciate any help.
    thanks,
    Meenal





    Tom Guest

  9. #9

    Default Re: Case statement help

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    Right now, the names of the data elements make no sense. The name
    "meeting_type" is fine, but "meeting_type_id" is absurd -- an identifier
    is unique and a type should have multiple occurences of its values.
    Please read ISO-11179.

    If you are trying to do some kind of count on the types of meetings,
    then put it into a VIEW, not a temp table that has to be constantly
    updated.
     [/ref]
    [sic] but say something like:
    WHEN 'X' THEN 3
    WHEN 'X' THEN 2
    Basically one value in the WHEN clause goes to 2 values in the THEN
    statement. <<

    NO! First, there is no such thing as a CASE *statement* in SQL; it is a
    CASE *expression* and by definition, an expression returns a scalar
    value. In this example, you will always get 3 for 'X' because that WHEN
    clause appears first.

    The CASE expression is an *expresion* and not a control statement; that
    is, it returns a value of one datatype. SQL-92 stole the idea and the
    syntax from the ADA programming language. Here is the BNF for a <case
    specification>:

    <case specification> ::= <simple case> | <searched case>

    <simple case> ::=
    CASE <case operand>
    <simple when clause>...
    [<else clause>]
    END

    <searched case> ::=
    CASE
    <searched when clause>...
    [<else clause>]
    END

    <simple when clause> ::= WHEN <when operand> THEN <result>

    <searched when clause> ::= WHEN <search condition> THEN <result>

    <else clause> ::= ELSE <result>

    <case operand> ::= <value expression>

    <when operand> ::= <value expression>

    <result> ::= <result expression> | NULL

    <result expression> ::= <value expression>

    The searched CASE expression is probably the most used version of the
    expression. The WHEN ... THEN ... clauses are executed in left to right
    order. The first WHEN clause that tests TRUE returns the value given in
    its THEN clause. And, yes, you can nest CASE expressions inside each
    other. If no explicit ELSE clause is given for the CASE expression,
    then the database will insert a default ELSE NULL clause. If you want
    to return a NULL in a THEN clause, then you must use a CAST (NULL AS
    <datatype>) expression. I recommend always giving the ELSE clause, so
    that you can change it later when you find something explicit to return.
    The <simple case expression> is defined as a searched CASE expression in
    which all the WHEN clauses are made into equality comparisons against
    the <case operand>. For example

    CASE iso__code
    WHEN 0 THEN 'Unknown'
    WHEN 1 THEN 'Male'
    WHEN 2 THEN 'Female'
    WHEN 9 THEN 'N/A'
    ELSE NULL END

    could also be written as:

    CASE
    WHEN iso__code = 0 THEN 'Unknown'
    WHEN iso__code = 1 THEN 'Male'
    WHEN iso__code = 2 THEN 'Female'
    WHEN iso__code = 9 THEN 'N/A'
    ELSE NULL END

    There is a gimmick in this definition, however. The expression

    CASE foo
    WHEN 1 THEN 'bar'
    WHEN NULL THEN 'no bar'
    END

    becomes

    CASE WHEN foo = 1 THEN 'bar'
    WHEN foo = NULL THEN 'no_bar' -- error!
    ELSE NULL END

    The second WHEN clause is always UNKNOWN.

    The SQL-92 Standard defines other functions in terms of the CASE
    expression, which makes the language a bit more compact and easier to
    implement. For example, the COALESCE () function can be defined for one
    or two expressions by

    1) COALESCE (<value exp #1>) is equivalent to (<value exp #1>)

    2) COALESCE (<value exp #1>, <value exp #2>) is equivalent to

    CASE WHEN <value exp #1> IS NOT NULL
    THEN <value exp #1>
    ELSE <value exp #2> END

    then we can recursively define it for (n) expressions, where (n >= 3),
    in the list by

    COALESCE (<value exp #1>, <value exp #2>, . . ., n), as equivalent to:

    CASE WHEN <value exp #1> IS NOT NULL
    THEN <value exp #1>
    ELSE COALESCE (<value exp #2>, . . ., n)
    END

    Likewise, NULLIF (<value exp #1>, <value exp #2>) is equivalent to:

    CASE WHEN <value exp #1> = <value exp #2>
    THEN NULL
    ELSE <value exp #1> END

    You can probably get a little more speed with this code:

    CASE
    WHEN meeting_type IN ('A','R') THEN 0
    WHEN 'Z' THEN 1
    WHEN meeting_type IN ('T', 'V', 'W', 'X', 'Y', 'P', 'S', 'Q')
    THEN 3
    WHEN meeting_type = 'C' THEN 4
    WHEN meeting_type = 'U' THEN 6
    ELSE CAST(meeting_type AS INTEGER) END

    Sort the WHEN clauses and IN() lists by likelihood. Did you noptice you
    were checking 'S' twice in the original code?

    --CELKO--


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. sql select case statement
    By Anderson11983 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 9th, 01:50 PM
  2. List in case statement
    By lingo smith in forum Macromedia Director Basics
    Replies: 2
    Last Post: April 17th, 02:05 AM
  3. Case Statement
    By tsetliff webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 9
    Last Post: February 5th, 11:26 PM
  4. Problems with CASE statement
    By Sean C. in forum IBM DB2
    Replies: 9
    Last Post: October 15th, 06:03 AM
  5. CASE statement and CONTAINS
    By steve in forum Macromedia Director Lingo
    Replies: 2
    Last Post: July 31st, 12:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139