1

I want to transfer SQL to SQLAlchemy and have the case that I have nested case statements.

The simple case is working: 

stmt = sqlalchemy.select([self.tusg_view_specials]).where(
    sqlalchemy.case([
        (self.tusg_view_specials.c.webo_close_date >= (datetime.date.today() - datetime.timedelta(days=30)), 1),
        (self.tusg_view_specials.c.wo_closed_date >= (datetime.date.today() - datetime.timedelta(days=61)), 1)
        ], else_=0),
    )

But when I have a nested case, means that the THEN is a case clause instead of a simple value: 

stmt = sqlalchemy.select([self.tusg_view_specials]).where(
    sqlalchemy.case([
        (self.tusg_view_specials.c.webo_close_date >= (datetime.date.today() - datetime.timedelta(days=30)), 1),
        (self.tusg_view_specials.c.wo_closed_date >= (datetime.date.today() - datetime.timedelta(days=61)), 1),
        (self.tusg_view_specials.c.work_order_number is None,
            sqlalchemy.case([(self.tusg_view_specials.c.flag_is_abw == 1, 1)], else_=0))
        ], else_=0), <<-- This line is shown to cause the error
    )

I get the following error message, I don't know how to deal with it:

sqlalchemy.exc.ArgumentError: Ambiguous literal: False.  Use the 'text()' function to indicate a SQL expression literal, or 'literal()' to indicate a bound value.

I can read the text, but don't know how to interpret it. Search results on "nested case" on SQLAlchemy are very little to none.

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
mstuebner
  • 406
  • 2
  • 15
  • 2
    The problem is not the nested case, but `self.tusg_view_specials.c.work_order_number is None`. The identity operator cannot be overloaded, and so that expression evaluates to False in Python. Did you mean `self.tusg_view_specials.c.work_order_number.is_(None)`? – Ilja Everilä Mar 08 '18 at 18:43
  • Possible duplicate of [Select NULL Values in SQLAlchemy](https://stackoverflow.com/questions/5602918/select-null-values-in-sqlalchemy) – Ilja Everilä Mar 09 '18 at 05:32

1 Answers1

0

As also Ilja Everilä wrote, the cause is the "IS None", which needs to be replaced by 

table_instance.c.work_order_number.is_(None)

or 

table_instance.c.work_order_number == None
mstuebner
  • 406
  • 2
  • 15