CODEWITHSUNDEEP
sqlsql-servert-sqlcase
hermann
hermann

Reputation: 6295

SQL CASE between values of the same table

I have the following @temp table :

SELECT * 
FROM @temp
WHERE LinkingID = 1

------------------------------------------------------------------
|Condition_ID   LinkingID   Operator   Variable1    Variable2    |
|     1             1          ==        25             2        |
|     2             1          <>        3              7        |
|     3             1          ==        4              4        |
------------------------------------------------------------------

I want to implement a CASE ( ??? ) that will check if Variable1 OPERATOR Variable2 is true, make the check for all rows and IF all rows are true then do something, else break off and abort.

I was thinking something like this ( I'm not sure how to express this in T-SQL so I use pseudolanguage ) :

CASE 
    WHEN Operator LIKE '=='
        THEN IF Variable1 == Variable2 THEN TRUE
        ELSE THEN FALSE
    WHEN Operator LIKE '<>'
        THEN IF Variable1  <> Variable2 THEN TRUE
        ELSE THEN FALSE
FROM @temp

If the result of this is true for all rows, then do something, else do something else

In the scenario above :
Row1 would return FALSE
Row2 would return TRUE
Row3 would return TRUE

Hence the result is FALSE.

Upvotes: 0

Views: 3168

Answers (3)

Robert Koritnik
Robert Koritnik

Reputation: 105059

Accumulating comparison results over the whole table

Checking that the whole table returns true for all rows is best done to count false ones and check whether that number is greater equal to 0. If it's 0 then it means that all rows matched otherwise number indicates number of rows that failed

Mind the inverted comparison compared to Operator value to catch negatives:

declare @NonMatched int

select
    @NonMatched = sum(
        case
            when [Operator] = '==' and Variable1 <> Variable2 then 1
            when [Operator] = '<>' and Variable1 = Variable2 then 1
            else 0
        end
    )
from @temp
where LinkingID = 1

if @NonMatched = 0
begin
    -- execute your stored procedure or whatever else set of statements
end

Here's also a SQLFiddle that's based on upper code.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

I would write something like this:

SELECT 
    CASE WHEN NOT EXISTS
           ( SELECT *
             FROM @temp
             WHERE LinkingID = 1
               AND NOT ( Operator = '=='  AND  Variable1 = Variable2
                      OR Operator = '<>'  AND  Variable1  <> Variable2
                       )
           ) THEN 'TRUE' 
             ELSE 'FALSE'
     END AS Result ;

Upvotes: 0

Jamiec
Jamiec

Reputation: 136144

Im going to go out on a limb and guess you're trying to do something with all rows which the conditional variable 1 [operator] variable2 evaluates true. This could be selecting them, or deleting them - it makes no difference really.

I'll use a select as the example:

SELECT * 
FROM @temp
WHERE LinkingID = 1
AND CASE Operator
    WHEN '==' THEN Variable1 = Variable2
    WHEN '<>' THEN Variable1 <> Variable2
END

The same where clause could be used if you wanted to delete these records.

EDIT To aggregate up this set and do something when all conditions for a particular LinkingID evaluate true, something like this will work:

IF EXISTS(
  SELECT LinkingID FROM temp
  WHERE LinkingID = 1
  GROUP BY LinkingId
  HAVING SUM(CASE 
    WHEN Operator = '==' AND Variable1 = Variable2 THEN 1 
    WHEN Operator = '<>' AND Variable1 <> Variable2  THEN 1
    ELSE 0
  END) = count(*)
)
BEGIN
  -- Do something
END

Live example to play with here: http://www.sqlfiddle.com/#!6/26d72/6 In that example I have set up LinkingID=2 where all conditions are true. Change the LinkingId=1 to =2 to see the do something message.

Upvotes: 1

Related Questions