| 52 | |
| 53 | @testing.requires.subqueries |
| 54 | def test_case(self, connection): |
| 55 | info_table = self.tables.info_table |
| 56 | |
| 57 | inner = select( |
| 58 | case( |
| 59 | (info_table.c.pk < 3, "lessthan3"), |
| 60 | (and_(info_table.c.pk >= 3, info_table.c.pk < 7), "gt3"), |
| 61 | ).label("x"), |
| 62 | info_table.c.pk, |
| 63 | info_table.c.info, |
| 64 | ).select_from(info_table) |
| 65 | |
| 66 | inner_result = connection.execute(inner).all() |
| 67 | |
| 68 | # Outputs: |
| 69 | # lessthan3 1 pk_1_data |
| 70 | # lessthan3 2 pk_2_data |
| 71 | # gt3 3 pk_3_data |
| 72 | # gt3 4 pk_4_data |
| 73 | # gt3 5 pk_5_data |
| 74 | # gt3 6 pk_6_data |
| 75 | eq_( |
| 76 | inner_result, |
| 77 | [ |
| 78 | ("lessthan3", 1, "pk_1_data"), |
| 79 | ("lessthan3", 2, "pk_2_data"), |
| 80 | ("gt3", 3, "pk_3_data"), |
| 81 | ("gt3", 4, "pk_4_data"), |
| 82 | ("gt3", 5, "pk_5_data"), |
| 83 | ("gt3", 6, "pk_6_data"), |
| 84 | ], |
| 85 | ) |
| 86 | |
| 87 | outer = select(inner.alias("q_inner")) |
| 88 | |
| 89 | outer_result = connection.execute(outer).all() |
| 90 | |
| 91 | assert outer_result == [ |
| 92 | ("lessthan3", 1, "pk_1_data"), |
| 93 | ("lessthan3", 2, "pk_2_data"), |
| 94 | ("gt3", 3, "pk_3_data"), |
| 95 | ("gt3", 4, "pk_4_data"), |
| 96 | ("gt3", 5, "pk_5_data"), |
| 97 | ("gt3", 6, "pk_6_data"), |
| 98 | ] |
| 99 | |
| 100 | w_else = select( |
| 101 | case( |
| 102 | [info_table.c.pk < 3, cast(3, Integer)], |
| 103 | [and_(info_table.c.pk >= 3, info_table.c.pk < 6), 6], |
| 104 | else_=0, |
| 105 | ).label("x"), |
| 106 | info_table.c.pk, |
| 107 | info_table.c.info, |
| 108 | ).select_from(info_table) |
| 109 | |
| 110 | else_result = connection.execute(w_else).all() |
| 111 | |