CODEWITHSUNDEEP
sqlt-sqlconditional-statementscasenested-if
Tami
Tami

Reputation: 19

How do I get my t-sql code to check for a condition then stop? not check the next condition. Return should be only one result

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..

Upvotes: -2

Views: 79

Answers (1)

Moe Sisko
Moe Sisko

Reputation: 12015

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

Upvotes: 1

Related Questions