How do I use the IF…ELSE condition in a WHERE clause?

Question

I want to merge the condition result of my 2 If statements. First if statement result shows only both 0 result, in second if statement result one of them has to be bigger than 0... Want I want to do is Leave second condition as it is and modify first if statament like my code...

 If (Inventory) <> 0 Then 
    If Apple = "" And Banana = "" Then
        strSQL = strSQL & " AND (myApple = 0 AND myBanana = 0)"
    End If
End If

 If int(Inventory) <> -1 Then   
    If Apple = "" And Banana = "" Then
        strSQL = strSQL & " AND (myApple <> 0 OR myBanana <> 0)"
    End If
End If

Here is a small table showing what my two conditions lead to: What I want to get

Inventory  |  Desired result
-----------+---------------
-1         |  show both zero or at least one not be zero (Show all)
 0         |  at least one must not be zero |

the [`CASE`](http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/case_statement.htm) statement — canon, Nov 15 '13 at 14:19
So you are conditionally creating a SQL statement based on the value of inventory, apple, and banana. Are you trying to move the entire statement into SQL? It's not clear what you want to do. — Joe, Nov 15 '13 at 14:22
So you are conditionally creating a SQL statement based on the value of inventory, apple, and banana: YES — user2611251, Nov 15 '13 at 14:25

score 2 · Answer 1 · edited May 23 '17 at 12:12

2

Generally within a WHERE clause you must us CASE.

How to use If Statement in Where Clause in SQL?

They provide the relevant links. So you will have to write.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 15 '13 at 14:20

Ted Johnson

4,315
3
29
31

Thorsten Kettner · Answer 2 · 2013-11-15T15:25:50.727

Here is a small table showing what your two conditions lead to:

Inventory  |  Desired result
-----------+---------------
-1         |  both must be zero
 0         |  at least one must not be zero
all others |  both must be zero and at least one must not be zero

You see that any number that is not 0 or -1 leads to a condition that can never be fulfilled. Think your request over, draw a table such as mine and then write your if statement accordingly. What language are you using there? Is that a Basic dialect? Maybe there is a CASE, SWITCH or ELSEIF structure available to have your conditions more readable.

EDIT: according to your edited question (but as said, once you have a table of desired results, to write an if statement is easy and you could have done that by yourself):

IF Inventory = 0 THEN
  strSQL = strSQL & " AND (myApple <> 0 OR myBanana <> 0) "
END IF

No need to check for Inventory = -1, because "both zero or at least one not be zero" means all (both zero, one zero or both not zero). (OR do you want to check for NOT NULL then?)

Sorry, you misunderstood. This was my _answer_. You didn't have to update your question, but think over what results you want and write your if statement accordingly. Now that you've updated your question it even shows more plainly: What condition do you want for Inventories other than 0 and -1? — Thorsten Kettner, Nov 15 '13 at 15:18
I think you are right for not checking the Inventory = -1, When I want to see the all result...either bot 0 or one of them not 0...So I need to use only second condition which is inventory=0. — user2611251, Nov 15 '13 at 15:39

How do I use the IF…ELSE condition in a WHERE clause?

2 Answers2