Select case - using "in" instead of "="

  • Ok guys, going nuts here.

    Let me explain the aim here:

    What I'm trying to accomplish is,

    make sql select values '01x', '02x' in line "and left(i.itemID,3) in

    based on the case that follows.

    In English (cause I cant say it in tsql no matter how hard I try): If PartnerIDs are in (113, 114) then left(i.ItemID,3) should be in ('01x','02x'), otherwise just '01x' will do.

    Select d.ID, i.ItemID

    From Documents d

    inner join Items i on i.OrderID=d.ID

    Where

    d.Sync is NULL

    and d.DocTypeID=1

    and left(i.ItemID,3) in

    (

    case when d.PartnerID in (113,114) then

    '01x', '02x' <-- This (obviosily) doesn't work

    -- (select '(' + char(39)+'01x'+char(39)+')' ) <-- this neither

    else

    '01x' -- This however does. But I need a way of using both '01x' and '02x' values in the "pre ELSE" line.

    end

    )

    Thanks in advance folks !

  • daliborh (11/10/2015)


    Ok guys, going nuts here.

    Let me explain the aim here:

    What I'm trying to accomplish is,

    make sql select values '01x', '02x' in line "and left(i.itemID,3) in

    based on the case that follows.

    In English (cause I cant say it in tsql no matter how hard I try): If PartnerIDs are in (113, 114) then left(i.ItemID,3) should be in ('01x','02x'), otherwise just '01x' will do.

    Select d.ID, i.ItemID

    From Documents d

    inner join Items i on i.OrderID=d.ID

    Where

    d.Sync is NULL

    and d.DocTypeID=1

    and left(i.ItemID,3) in

    (

    case when d.PartnerID in (113,114) then

    '01x', '02x' <-- This (obviosily) doesn't work

    -- (select '(' + char(39)+'01x'+char(39)+')' ) <-- this neither

    else

    '01x' -- This however does. But I need a way of using both '01x' and '02x' values in the "pre ELSE" line.

    end

    )

    Thanks in advance folks !

    Try this:

    AND (

    (d.PartnerID IN (113,114) AND LEFT(i.ItemID,3) IN ('01x', '02x'))

    OR

    LEFT(i.ItemID,3) IN ('01x')

    )

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks Chris!

    Looks like a solution.

    Works a treat in a code snippet.

    Have to apply it inside my sp, but I don't see why it shouldn't work.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply