Repository: kayak/pypika
Pull request #26 (2016-12-08 13:34:40)

Fixes for Case statements plus Documentation
Removed requirement for the Else clause in Case statements since it is not required by SQL.   Added documentation for the Cases statement.


Patch:
diff --git a/README.rst b/README.rst
index 58884ea..2154b78 100644
--- a/README.rst
+++ b/README.rst
@@ -414,6 +414,33 @@ There are several string operations and function wrappers included in |Brand|.
 
     SELECT id,CONCAT(fname, ' ', lname) full_name FROM customers
 
+Case Statements
+"""""""""""""""
+
+Case statements allow fow a number of conditions to be checked sequentially and return a value for the first condition
+met or otherwise a default value.  The Case object can be used to chain conditions together along with their output
+using the ``when`` method and to set the default value using ``else_``.
+
+
+.. code-block:: python
+
+    from pypika import Case, functions as fn
+
+    customers = Tables('customers')
+    q = Query.from_(customers).select(
+        customers.id,
+        Case()
+           .when(customers.fname == "Tom", "It was Tom")
+           .when(customers.fname == "John", "It was John")
+           else_("It was someone else.").as_('who_was_it'),
+    )
+
+
+.. code-block:: sql
+
+    SELECT "id",CASE WHEN "fname"='Tom' THEN 'It was Tom' WHEN "fname"='John' THEN 'It was John' ELSE 'It was someone else.' END "who_was_it" FROM "customers"
+
+
 
 Inserting Data
 ^^^^^^^^^^^^^^
diff --git a/pypika/terms.py b/pypika/terms.py
index c641bb3..9d3cfd1 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -295,6 +295,7 @@ class Criterion(Term):
     def get_sql(self):
         raise NotImplementedError()
 
+
 class BasicCriterion(Criterion):
     def __init__(self, comparator, left, right, alias=None):
         """
@@ -529,17 +530,19 @@ class Case(Term):
     def get_sql(self, with_alias=False, **kwargs):
         if not self._cases:
             raise CaseException("At least one 'when' case is required for a CASE statement.")
-        if self._else is None:
-            raise CaseException("'Else' clause is requred for a CASE statement.")
-
-        return 'CASE {cases} ELSE {else_clause} END{alias}'.format(
-            cases=" ".join('WHEN {when} THEN {then}'.format(
-                when=criterion.get_sql(**kwargs),
-                then=term.get_sql(**kwargs)
-            ) for criterion, term in self._cases),
-            else_clause=self._else.get_sql(**kwargs),
-            alias=' \"{}\"'.format(self.alias) if self.alias is not None and with_alias else ''
-        )
+
+        cases = " ".join('WHEN {when} THEN {then}'.format(
+            when=criterion.get_sql(**kwargs),
+            then=term.get_sql(**kwargs)
+        ) for criterion, term in self._cases)
+        else_ = (' ELSE {}'.format(self._else.get_sql(**kwargs))
+                 if self._else
+                 else '')
+        alias = (' \"{}\"'.format(self.alias)
+                 if self.alias is not None and with_alias
+                 else '')
+
+        return 'CASE {cases}{else_} END{alias}'.format(cases=cases, else_=else_, alias=alias)
 
     def fields(self):
         fields = []


Test patch:
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index 68c617d..4a7651a 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -243,6 +243,11 @@ class AggregationTests(unittest.TestCase):
 
 class ConditionTests(unittest.TestCase):
     def test__case__raw(self):
+        q = Q.from_('abc').select(Case().when(F('foo') == 1, 'a'))
+
+        self.assertEqual("SELECT CASE WHEN \"foo\"=1 THEN 'a' END FROM \"abc\"", str(q))
+
+    def test__case__else(self):
         q = Q.from_('abc').select(Case().when(F('foo') == 1, 'a').else_('b'))
 
         self.assertEqual("SELECT CASE WHEN \"foo\"=1 THEN 'a' ELSE 'b' END FROM \"abc\"", str(q))
@@ -270,12 +275,6 @@ class ConditionTests(unittest.TestCase):
 
             str(q)
 
-    def test__case__no_else(self):
-        with self.assertRaises(CaseException):
-            q = Q.from_('abc').select(Case().when(F('foo') > 0, F('fiz')))
-
-            str(q)
-
 
 class StringTests(unittest.TestCase):
     t = T('abc')