[SOLVED] SQLAlchemy Dealing with null/none type in where statement

SQLAlchemy Dealing with null/none type in where statement

I have a query that I am attempting to convert from Oracle into sqlalchemy, but I'm running into issues with this time conversion part. There is a chance that the conversion value is Null and so needs to be treated like 0 if so.

stmt = (
    select(QcSodTable)
    .join(
        LocalTimeTable,
        and_(
            QcSodTable.platformid == LocalTimeTable.platformid,
            QcSodTable.networktype == LocalTimeTable.networktype
        ),
        isouter=True,
    )
    .where(        
        ((QcSodTable.datetime + cast(literal(1.5) + ' DAYS', Interval)) - (LocalTimeTable.time_conv / 24) < func.now(),
    )

This is the query as is right now. If the time_conv exists, this works as expected. If time conv is Null, it returns no results, if I take out the time_conv part, it returns as I expect. Is there a way to force time_conv to act as 0 when it is Null?

Solution

Wrap LocalTimeTable.time_conv with func.coalesce(LocalTimeTable.time_conv, 0), which returns time_conv if it's not NULL...and returns 0 if time_conv is NULL

stmt = (
    select(QcSodTable)
    .join(
        LocalTimeTable,
        and_(
            QcSodTable.platformid == LocalTimeTable.platformid,
            QcSodTable.networktype == LocalTimeTable.networktype
        ),
        isouter=True,
    )
    .where(        
        ((QcSodTable.datetime + cast(literal(1.5) + ' DAYS', Interval)) - (func.coalesce(LocalTimeTable.time_conv, 0) / 24)) < func.now()
    )
)