[RESOLVED] CASE Statment in SQL WHERE Clause

[Mark Gambo]

I have the following SQL String:

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?

[szlamany]

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

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

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

[Mark Gambo]

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

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

[Mark Gambo]

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

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

[szlamany]

So would this do it?

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

[Mark Gambo]

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

[szlamany]

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...

[Mark Gambo]

When you specify @CaseNum or @Unit?

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

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!

[szlamany]

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...

[Mark Gambo]

Small change to you 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

[Mark Gambo]

BTW Steve:



Sorry

[szlamany]

Glad it worked for you!

[Mark Gambo]

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

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

[szlamany]

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...

[Mark Gambo]

Thanks for your help again Steve.