Conditionally evaulate a condition SQL

Question

I have an employee table from which I retrieve records using a stored procedure now I need to add a condition in the stored procedure --

Only if employeeType='MD' then check if employeeReportableIndicator='N' , if 'N' then do not fetch this record.

This implicitly means --

1)if employeeType!='MD' then I should fetch the record regardless of employeeReportableIndicator column value .

2)if employeeType='MD' and employeeReportableIndicator='Y' fetch the value .

To simplify the stored procedure I am just writing a small query covering the above condition .Here is what I tried till now

select * 
from   employee 
where  somecondition1 
and    somecondition2 
and    CASE when employeeType='MD' 
            then (CASE when employeeReportableIndicator='N' 
                       then false 
                       else true 
                  END) 
       END

The above is giving me syntax error

an unexpected token "END-OF-STATEMENT" was found following "else true END ) END".Expected token may include: "".SQLSTATE=42601

Also is my approach for writing the query correct ,please suggest if any better solution. I am using DB2 and Sybase

score 0 · Answer 1 · answered Sep 25 '17 at 18:20

0

You don't need CASE for this, just an OR condition:

Select  *
From    Employee
Where   SomeCondition1
And     SomeCondition2
And     
(   
    EmployeeType <> 'MD'
    Or  
    (   
        EmployeeType = 'MD'
    And EmployeeReportableIndicator = 'Y'
    )
)

answered Sep 25 '17 at 18:20

Siyual

16,415
8
44
58

Can be simplified. – Clockwork-Muse Sep 25 '17 at 20:58
@Clockwork-Muse Care to elaborate? Even if it can be simplified somehow, the intent is perfectly clear as written. – Siyual Sep 25 '17 at 21:02
There are four possible cases for the last conditions: MD+Y, MD+N, !MD+Y, !MD+Y. If !MD, we consider the condition fulfilled (don't care about Y/N), which eliminates half the table. This leaves just MD+Y and MD+N... where the only distinct part is Y/N. At that point, checking whether MD is frivolous, thus: `AND employeeType <> 'MD' OR employeeReportableIndicator = 'Y'` – Clockwork-Muse Sep 25 '17 at 21:50

score 0 · Answer 2 · answered Sep 25 '17 at 18:22

0

Instead of a case when you shoul use an AND - OR in where

select * 
from   employee 
where  somecondition1 
and    somecondition2 
and    ( ( employeeType='MD' AND employeeReportableIndicator='Y') 
           OR ( employeeType!='MD ))

answered Sep 25 '17 at 18:22

ScaisEdge

131,976
10
91
107

Can be simplified. – Clockwork-Muse Sep 25 '17 at 20:59

Conditionally evaulate a condition SQL

2 Answers2