Search code examples
pythonpandassqlitems-accesspandasql

Python - Pandasql - IIf Function doesn't work Error

Trying to execute the following code:

import pandas as pd
import pandasql as ps

df = pd.DataFrame({'A':[5,6,7], 'B':[7,8,9]})
print(df)

       A  B
    0  5  7
    1  6  8
    2  7  9

qry = """SELECT df.*, IIf(A Is Null,[B],[A]) AS NEW_A FROM df;"""
df1 = ps.sqldf(qry, globals())
print(df1)

yields this error:

PandaSQLException: (sqlite3.OperationalError) no such function: IIf 
[SQL: 'SELECT df.*, IIf(A Is Null,[B],[A]) AS NEW_A FROM df;']

I've tried various combinations of different syntax regarding square brackets, globals/locals etc. but couldn't find the issue. Is this function simply not existing?

I simply copied the SQL query from an MS Access query but for other cases this works fine.

Solution

  • There is no iif() function in SQLite.
    In this case you can use coalesce():

    SELECT df.*, coalesce(A, B) AS NEW_A FROM df

    The functionality of iif() can be achieved with a CASE statement:

    SELECT df.*, CASE WHEN A is null THEN B ELSE A END AS NEW_A FROM df

    but in this case coalesce() is simpler.

    UPDATE:
    Starting from version 3.32.0 of SQLite (2020-05-22) the function iif() is supported.