Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    51

    Unanswered: Cases with IN statement

    I'm new to SQL server, and have done most of my querying in Access.

    I know I'm suppose to use the Case statement instead of the IIF function that Access provides. However it doesn't seem to work the way I am using it.

    I have a stored proc that uses

    Insert INTO #tempTable (prefix, name, suffix, qty)
    SELECT cp.prefix, cp.body, cp.suffix, Case When invS.Product In ('x', 'y','z', 'zz') 0 else invS.OrderQty From inventory as invs INNER JOIN combinedproduct as cp ON (invs.prefix = cp.prefix) AND (invs.name = cp.Name) AND (invs.suffix = cp.suffix) GROUP BY cp.prefix, cp.body, cp.suffix

    can anyone tell me what I'm doing wrong?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    Insert INTO #tempTable (
       prefix, name, suffix
    ,  qty)
       SELECT cp.prefix, cp.body, cp.suffix
    ,     CASE
             WHEN invS.Product In ('x', 'y','z', 'zz') THEN 0
             ELSE invS.OrderQty
          END
          FROM inventory as invs
          INNER JOIN combinedproduct as cp
             ON (invs.prefix = cp.prefix)
             AND (invs.name = cp.Name)
             AND (invs.suffix = cp.suffix)
          GROUP BY cp.prefix, cp.body, cp.suffix
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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