November 10, 2015 at 2:17 am
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 !
November 10, 2015 at 2:33 am
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')
)
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
November 10, 2015 at 5:10 am
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