CODEWITHSUNDEEP
sqlfirebird
TheWesDias
TheWesDias

Reputation: 293

What's the difference between these SQL conditions?

Select * from Example
where
1 = Case when :index = 0 then
         Case when DateEx Between :pDat1 and :pDate2 then 1 end
    else
         Case When :index = 1 or :index = 2 then
              Case When DateEx >= :pDat1 then 1 end
         end
    end

And

Select * from Example
where 
1 = Case when :index = 0 then
            Case when DateEx Between :pDat1 and :pDat2 then 1 end
    else 1
    end
and 1 = Case When :index = 1 or :index = 2 then
             Case When DateEx >= :pDat1 then 1 end
        end

Upvotes: 0

Views: 852

Answers (2)

Andrej Kirejeŭ
Andrej Kirejeŭ

Reputation: 5481

You could rewrite the first condition:

(:index = 0 AND DateEx BETWEEN :pDat1 AND :pDat2) 
OR 
(:index IN (1, 2) AND DateEx >= :pDat1)

The second condition can be rewritten:

(:index <> 0 OR DateEx BETWEEN :pDat1 AND :pDat2) 
AND
(:index IN (1, 2) AND DateEx >= :pDat1)

It is obvious that the second condition can be reduced to:

:index IN (1, 2) AND DateEx >= :pDat1

So, the difference is that first query deals with situation when :index = 0 and the second works only for :index = 1 OR :index = 2.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

The first example implements an OR condition.

It is true for the following cases...
A). (:index = 0) AND (DateEx Between :pDat1 and :pDate2)
B). (:index = 1 or :index = 2) AND (DateEx >= :pDat1)

It's A OR B.


The second is less obvious.

It has two parts, seperated by AND. But it has an extra ELSE in what would be A in the first version.

It's true if BOTH of the following are true...
A) ((:index = 0) AND (DateEx Between :pDat1 and :pDat2)) OR (:index <> 0)
B) ((:index = 1 or :index = 2) AND (DateEx >= :pDat1)

But that's slightly broken. Because if :index = 0, then B can never be true.


In fact, you don't need any CASE statements at all, just use this...

WHERE
  ((:index = 0)               AND (DateEx Between :pDat1 and :pDate2))
  OR
  ((:index = 1 or :index = 2) AND (DateEx >= :pDat1))

Upvotes: 3

Related Questions