Pseudo:
If condition is true, return results and don't look for next condition.
If condition is false, then check for the next condition.
etc ...I have like 6 of these to check in a hierarchy order.
Case check every condition and so instead of stopping at the first one it goes on and there are multiple trues in the set however, I only want the first one!
Nest IFF is getting me the same thing. (more than one answer)
IIF((t.testkey = 141 AND wordsalad <3) OR (t.testkey = 141 AND wordsalad>10),'Found it 1',
iif(t.testkey = 821 AND wordsalad <20,'Found it 2',
iif(t.testkey = 725 AND wordsalad<1.0030,'Fouind it 3',
iif(t.testkey = 725 AND wordsalad>1.025,'Found it 4',
iif(t.testkey = 810 AND wordsalad<10,'Found it 5',
iif(t.testkey = 809 AND word salad<10,'found it 6',
'Didn't find it')))))),
How do I write so it stops after the first condition it finds and not go on?
This is for case:
CASE
WHEN (t.testkey = 141 AND wordsalad <3) OR (t.testkey = 141 AND
wordsalad >10.0)) then 'Found it 1'
WHEN (t.testkey = 821 AND wordsald <20) then 'Found it 2'
WHEN (t.testkey = 725 AND wordsalad <1.0030) then 'Found it 3'
WHEN (t.testkey = 725 AND wordsalad >1.025) then 'Found it 4'
WHEN (t.testkey = 810 AND wordsalad <10) then 'Found it 5'
WHEN (t.testkey = 809 AND wordsalad <10) then 'Found it 6'
Else 'Didn't find it'
END AS [Conditions2]
data looks like this:
Test | TestKey | wordsalad |
Test1| TestKey141 | 10.2 |
Test2| TestKey821 | 4 |
test3| TestKey725 | 0 |
etc..
I'm kind of guessing what you are after exactly, but maybe something like this (?):
create table sometable (id int primary key, testkey int, wordsalad decimal(18,6))
insert sometable (id, testkey, wordsalad) values (1, 821, 17)
insert sometable (id, testkey, wordsalad) values (2, 725, 0.5)
GO
-- return the row that matches the most preferred condition.
-- If none of the rows matched any of the conditions, then no row is returned.
select top 1 * from sometable t
cross apply (
select
CASE
WHEN (t.testkey = 141 AND wordsalad <3) OR (t.testkey = 141 AND
wordsalad >10.0) then 1 -- most preferred condition
WHEN (t.testkey = 821 AND wordsalad <20) then 2
WHEN (t.testkey = 725 AND wordsalad <1.0030) then 3
WHEN (t.testkey = 725 AND wordsalad >1.025) then 4
WHEN (t.testkey = 810 AND wordsalad <10) then 5
WHEN (t.testkey = 809 AND wordsalad <10) then 6 -- least preferred condition
Else NULL -- not matched
END AS cond
) c
where c.cond IS NOT NULL
order by c.cond