[RESOLVED] CASE Statment in SQL WHERE Clause

I have the following SQL String:

VB Code:

Declare @UNIT NVARCHAR(50)
Declare @CASENUM NVARCHAR(50)
 
SET @UNIT = Null /*'Sales'*/
Set @CASENUM = Null /*'C0020'*/
 
SELECT INC.Unit, INC.CASENUM
FROM INCIDENTS INC INNER JOIN CASES CS ON INC.CASENUM = CS.CASENUM 
WHERE 
       (CASE @CASENUM 
        WHEN Null THEN 1
        WHEN @CASENUM THEN (CASE WHEN INC.CASENUM = @CASENUM THEN 1 ELSE 0 END) END) = 1
    OR
       (CASE @UNIT 
        WHEN Null THEN 1
        WHEN @UNIT THEN (CASE WHEN INC.UNIT = @UNIT THEN 1 ELSE 0 END) END) = 1
ORDER BY INC.UNIT

This code works fine as long as I supply at least one of the Variables, but What I want to do if both variables are null I want all of the records returned. When both variables are omitted this SQL String returns no records.

Any ideas?

Doesn't this do the same thing and handle the "both supplied situation?

Code:

---

Where (@CaseNum is Null or @CaseNum=Inc.CaseNum)
    and (@InvUnit is Null or @InvUnit=Inc.Unit)

---

btw - what's with @InvUnit and @Unit?

Quote:

---

Originally Posted by szlamany

. . .btw - what's with @InvUnit and @Unit?

---

Thanks again, Just a typo @InvUnit is really @Unit in disguise

Steve I forgot one statement in the sql string, if the @Unit is not Null I want all records that equal the @UNIT variable and that rows Status Column doesn't contain 'Completed'. If the @Unit is null I want all of the records returned based upon

VB Code:

Declare @UNIT NVARCHAR(50)
Declare @CASENUM NVARCHAR(50)
 
SET @UNIT = Null /*'Sales'*/
Set @CASENUM = Null /*'C0020'*/
 
SELECT INC.Unit, INC.CASENUM
FROM INCIDENTS INC INNER JOIN CASES CS ON INC.CASENUM = CS.CASENUM 
WHERE 
       (CASE @CASENUM 
        WHEN Null THEN 1
        WHEN @CASENUM THEN (CASE WHEN INC.CASENUM = @CASENUM THEN 1 ELSE 0 END) END) = 1
    OR
       (CASE @UNIT 
        WHEN Null THEN 1
        WHEN @UNIT THEN (CASE WHEN (INC.UNIT = @UNIT AND [B]INC.STATUS <> 'Completed'[/B]) THEN 1 ELSE 0 END) END) = 1
ORDER BY INC.UNIT

So would this do it?

Code:

---

Where (@CaseNum is Null or @CaseNum=Inc.CaseNum)
    and (@Unit is Null or (@Unit=Inc.Unit and INC.STATUS <> 'Completed'))

---

No dice, I am getting all the records irregardless of each records Status type

Quote:

---

Originally Posted by Mark Gambo

No dice, I am getting all the records irregardless of each records Status type

---

When you specify @CaseNum or @Unit?

btw - the problem with your WHERE/CASE block logic might be because you are missing ELSE statements in the outer-CASE. Without an ELSE a CASE/block will return a null if conditions are not met...

Quote:

---

Originally Posted by szlamany

When you specify @CaseNum or @Unit?

---

When I don't specify either of these variables (Null)

Quote:

---

Originally Posted by szlamany

btw - the problem with your WHERE/CASE block logic might be because you are missing ELSE statements in the outer-CASE. Without an ELSE a CASE/block will return a null if conditions are not met...

---

Hmm, I'll take a look at that, thanks!

Code:

---

Where (@CaseNum is Null or @CaseNum=Inc.CaseNum)
    and (@Unit is Null or (@Unit=Inc.Unit and INC.STATUS <> 'Completed'))
    and (@CaseNum is not null or @Unit is not null)

---

So adding this third AND condition - that either of the two must not be null - should take care of that situation...

Small change to you code:

VB Code:

Where (@CaseNum is Null or @CaseNum=Inc.CaseNum)
    and ((@Unit is Null and INC.STATUS <> 'Completed') or (@Unit=Inc.Unit and INC.STATUS <> 'Completed'))

Thanks again Steve, you are a miracle worker

BTW Steve:

http://vbforums.com/attachment.php?a...id=52364&stc=1

Sorry :( :(

Glad it worked for you!

Nope spoke to soon, I came up with this workaround until I can figure it out:

VB Code:

Declare @UNIT NVARCHAR(50)
Declare @CASENUM NVARCHAR(50)
 
SET @UNIT =  Null --'Group 32'
Set @CASENUM = Null --'C01-0020'
 
IF @CASENUM is Not Null
    SELECT INC.Unit, INC.CASENUM, PCR.PRIMARY_CASE_DT, INC.STATUS
    FROM IADATA.dbo.INCIDENTS INC INNER JOIN eGrpMgmt.dbo.CASES CS ON INC.CASENUM = CS.CASENUM 
    INNER JOIN IADATA.IA_ADM.PRIMARY_CASE_REC PCR ON PCR.PC_INCNUM = INC.INCNUM
    Where Inc.CaseNum = @CaseNum
        ORDER BY INC.UNIT
 
IF @UNIT is Not Null
    SELECT INC.Unit, INC.CASENUM, PCR.PRIMARY_CASE_DT, INC.STATUS
    FROM IADATA.dbo.INCIDENTS INC INNER JOIN eGrpMgmt.dbo.CASES CS ON INC.CASENUM = CS.CASENUM 
    INNER JOIN IADATA.IA_ADM.PRIMARY_CASE_REC PCR ON PCR.PC_INCNUM = INC.INCNUM
    Where Inc.Unit = @UNIT and INC.STATUS <> 'Completed'
        ORDER BY INC.UNIT
 
IF @CASENUM is Null AND  @UNIT is Null
    SELECT INC.UNIT, INC.CASENUM, PCR.PRIMARY_CASE_DT, INC.STATUS
    FROM IADATA.dbo.INCIDENTS INC INNER JOIN eGrpMgmt.dbo.CASES CS ON INC.CASENUM = CS.CASENUM 
    INNER JOIN IADATA.IA_ADM.PRIMARY_CASE_REC PCR ON PCR.PC_INCNUM = INC.INCNUM
    Where INC.STATUS <> 'Completed'
        ORDER BY INC.UNIT

A little cumbersome but it works for me

Mark - that method has it's benefits...

The QUERY OPTIMIZER gets a much simpler WHERE clause to analyze.

It's probably easier to maintain in the future.

There is the downside of having to keep the three select's the same - but otherwise...

Thanks for your help again Steve.