Do a case on the result of a subquery

Question

I was wondering how to do this in SQL. From the result i get (RESULT 2nd column). I want to check if this result is in the result of my subquery (3rd column) then set 'Y' or 'N' in the SELECTED (4th column) for now, my SQL query looks like this :

Select ANALYTE as Value, RESULT
,(select DISTINCT CHARLIMITS from SPEC_ANALYTES where SPEC_ANALYTES.ANALYTE = @ANALYTE AND SPEC_ANALYTES.TESTCODE = @TESTCODE ) as subquery,
case when subquery then 'Y'
     else 'N'
End as SELECTED
from POSSIBLERESULTS
where   ANALYTE = @ANALYTE AND TESTCODE = @TESTCODE

What I have:

What I would like:

I hope I made it understandable ! Thanks for taking the time to help me

Thibault.

Answer 1

You may use LIKE with wildcard matching in your case statement. In order to not repeat the subquery, I've used your current query as a sub query see below:

SELECT
    *,
    CASE
       WHEN subquery LIKE CONCAT('%',RESULT,'%') THEN 'Y'
       ELSE 'N'
    END as SELECTED
FROM (
    SELECT
        ANALYTE as Value, 
        RESULT,
        (select DISTINCT CHARLIMITS from SPEC_ANALYTES where SPEC_ANALYTES.ANALYTE = @ANALYTE AND SPEC_ANALYTES.TESTCODE = @TESTCODE ) as subquery,
   FROM 
       POSSIBLERESULTS
   WHERE 
       ANALYTE = @ANALYTE AND TESTCODE = @TESTCODE
) t