Condition within Where Clause

Question

I am looking to add 'if else' condition within a where clause, but not sure how would I do it. Looking at the below table set, I am trying to filter the results where the query would extract all the Product line subtypes and add a condition only when the [Product Line Subtype] = 'Marine'. When it is Marine, then it should consider only two combinations of Section and Profit Code while omitting other combinations.

Combination 1 When Prod line Subtype = Marine then Section = Inland and Profit Code = Builders

Combination 2 When Prod line Subtype = Marine then Section = Ocean and Profit Code = Stocks

My actual table has larger sets of distinct combinations than showed in the below table when Prod line Subtype = Marine, but I just want to filter only the above two combinations to my results set. Any help would be much appreciated!

Main table

+--+------------------+-------------+-----------------+
|  |Prod line Subtype | Section     |  Profit Code    |
+--+------------------+-------------+-----------------+
|  | Marine           | Inland      |  Builders       |
|  | Marine           | Ocean       |  Stock          | 
|  | Property         | General     |  Transport      |  
|  | Energy           | Source      |  Others         |  
|  | Property         | General     |  Transport      |   
|  | Energy           | Source      |  Transport      |  
|  | Marine           | Inland      |  Transport      |    
|  | Marine           | Floaters    |  Transport      |    
|  | Marine           | Cargo       |  Others         |    
+--+------------------+-------------+---------------- +

Expected Results

+--+------------------+-------------+-----------------+
|  |Prod line Subtype | Section     |  Profit Code    |
+--+------------------+-------------+-----------------+
|  | Marine           | Inland      |  Builders       |
|  | Marine           | Ocean       |  Stock          | 
|  | Property         | General     |  Transport      |  
|  | Energy           | Source      |  Others         |  
|  | Property         | General     |  Transport      |   
|  | Energy           | Source      |  Transport      |  
+--+------------------+-------------+---------------- +

My query attempt:

select *
from #Step1 
where c1.row_ord = 1
and c1.[Prod Line Subtype]  = 'Marine' AND (
    (c1.[Section] = 'Inland' AND c1.[Profit Code] = 'Builder')
    OR (c1.[Section] = 'Ocean' AND c1.[Profit Code] = 'Stock')
)

You can use a case statement for this, what's your current query? — Jacob H, May 18 '17 at 14:10
It's generally better to use `AND`/`OR` instead of `case` _expressions_ in `WHERE` clauses. — jarlh, May 18 '17 at 14:15
Possible duplicate of [Conditional WHERE clause in SQL Server](http://stackoverflow.com/questions/18629132/conditional-where-clause-in-sql-server) — Tab Alleman, May 18 '17 at 15:01

Erick Lanford Xenes · Accepted Answer · 2017-05-18T14:32:42.507

1

What about:

    Select * from [Your table]
    Where ([Prod line Subtype]<>'Marine' Or 
       (Section='Inland' And [Profit Code]='Builders') Or
       (Section='Ocean' And [Profit Code]='Stocks')    
    )

Can omit the [Prod line Subtype]='Marine' from or conditions

edited May 18 '17 at 14:32

answered May 18 '17 at 14:18

Erick Lanford Xenes

1,467
2
20
34

Condition within Where Clause

1 Answers1