Sql case statement in where clause
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.
>> trying to use a CASE Statement in the Where Clause and I'm having difficulties. <<
Your first problem is that there is no CASE statement in SQL. This is a declarative language and we have a CASE expression. Expressions return scalar values. The CASE works by first finding the data type of the THEN and ELSE clause to use for the result. The WHEN clauses are tested in left-to-right order and the first TRUE is executed.
Your next is that you do not know that columns are not fields, so your skeleton code uses a bad word that shows your mindset. Also, why do you think that “condition_#” is a clear, precise, helpful data element name?
>> The WHERE Clause has three conditions. Condition1 AND either Condition2 or Condition
must be met. I use @in_low_cohesion_flg to determine whether Condition2 or Condition3 must be met. <<
This has nothing to with SQL; this poor software engineering. A well-designed module of code is coherent; it has one entry an one exit point, and it performs one well-defined task. What you have is a module with flag cohesion; it is controlled by external modules. Read Yourdon, DeMarco, Constantine, et al. The fact that the name of the module has an “or” in it is sign of bad programming. We call these thing “Automobiles, Squid and Lady Gaga” code.
CREATE PROCEDURE Operations_Or_Admin_Report
WHERE organizational_level = 'Regional'
AND CASE WHEN @in_low_cohesion_flg = 'Operations'
AND store_city_name = 'Seattle'
AND store_status = 'Primary'
WHEN @in_low_cohesion_flg = 'Admin'
AND office_city_name = 'Portland'
THEN 'T' ELSE 'F' END = 'T';
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQLSource: social.technet.microsoft.com