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

Question

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

@jarlh case goes through each and statement and multiple statements can be true but I wan't it to stop looking if it find the first one. Same if the nest. The first true statement it need to stop looking and display the comment — Tami, Aug 23 '23 at 16:37
@stu not sure what you mean? looking between 2 values <3 or >10 is there a better way to write that? t.testkey = 141 AND wordsalad <3) OR (t.testkey = 141 AND wordsalad<10 — Tami, Aug 23 '23 at 16:39
A CASE stops after the first WHEN evaluating to true has been found. — jarlh, Aug 23 '23 at 16:40
@jarlh it doesn't though it returns found it 1, found it 3, found it 5 case statement same Conditions2 NULL 2 3 5 — Tami, Aug 23 '23 at 16:42
Don't think so... https://dbfiddle.uk/9Zqui2dE, https://stackoverflow.com/tags/case/info — jarlh, Aug 23 '23 at 16:42
@jarlh is there something wrong with the case statement then? I added it. Is it because the test are different tests I am looking at? — Tami, Aug 23 '23 at 17:01
@stu it returns multiple answers in the output Conditions2 returns 4 rows. row 1 says "didn't find it", row 2 says, "Found it 2", row3 says, "Found it 3, row 4 says, "found it 5" technically those are all true but if it find the first one I want it not find that the other tests are true also. — Tami, Aug 23 '23 at 17:10
Is `t.testkey = 141 AND ( wordsalad < 3 or wordsalad > 10 )` inappropriate? Why the dancing data types, e.g. `wordsalad > 10` and `wordsalad > 10.0`? — HABO, Aug 23 '23 at 17:10
Sorry everyone I am an SSRS report writer but sometimes we need to change the query and my sql skills are basic. — Tami, Aug 23 '23 at 17:16
HABO I changed it to wordsalad as the is a big (CAST(COALESCE(CONVERT(XML, '<![CDATA[' + REPLACE(r.result, '***', ']]><![CDATA[') + ']]>').value('/root[1]/part[1]', 'varchar(25)'), '')as float) and it is way hard to read like that... with it in there. — Tami, Aug 23 '23 at 17:18
Based on your response to @stu Are you expecting it to return only the first row that matches a condition? A case statement evaluates each row and will return the matching value from the case for that row — NickW, Aug 23 '23 at 17:42
YES! @NickW case returns multiple matches. I need to find a way to write a query that returns only the first true statement it finds. In the same order as the case statement is written. Iff the first one is true then return that and then stop looking. if the first doesn't find it then look in the second ...etc Some kind of loop maybe? I have tried everything I know and then some. — Tami, Aug 23 '23 at 17:55
SQL doesn’t really work on individual records, it deals with record sets - so you’d need to write procedural logic (I.e. code) to achieve what you want. Do you just want a single record returned to do you want the first record - is there some order in which the records need to be passed through the code? However, this feels like an XY problem: your asking how to make a specific solution to a problem work whereas if you presented what the actual problem was that up you are trying solve, there may be a better solution — NickW, Aug 23 '23 at 18:53
thanks @NickW I don't know if an explanation will help. This is a medical-test validation query. So there are 6 different tests they do on one sample. First test would be is this pH level valid if its not....test over nothing else matters. if its valid then got to the next test Oxi. Is this test in valid range no. say no and stop nothing else matters, if yes then go to the next test. etc... till finally they all passed test is good. I can write some here and then some in the ssrs report again but I am not figuring it out any which way I write it. — Tami, Aug 23 '23 at 18:57
Does `wordsalad` (and `word salad`) represent the _same_ block of code everywhere you've used it or is it a placeholder for "arbitrary pieces of code I chose to omit, but in a way that leads you to believe something untrue"? If it is the same why would you compare it to `10` and `10.0`? Is it's data type shifting from `int` to `numeric(13,2)` or are you relishing that "A foolish consistency is the hobgoblin of little minds"? — HABO, Aug 23 '23 at 20:31
Presumably your data contains a sample identifier? I would convert your dataset to be one row per sample, containing the data for the 6 tests (using joins or a pivot) and then your case statement will work — NickW, Aug 23 '23 at 21:03

Moe Sisko · Accepted Answer · 2023-08-24T23:27:39.943

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

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

1 Answers1