Search code examples
t-sqljet-sql

Changing Jet SQL IsNull to to SQL IsNull Function

I have a query in MS Access that I am trying to change to SQL view

One of the select statement part is

IIf(IsNull([Book ID]),-1,[Book ID]) AS SubBookID

Unlike in Access T-SQL wants 2 parameters for the IsNull function.

What I need to do is something like

IIf(IsNull([Book ID],true),-1,[Book ID]) AS SubBookID

But we cannot use true like that cause T-SQL thinks that it is a column name

Solution

  • you are going to check if [Book ID] is null or not. If it is null then you are going to return -1 else you are going to return the [Book ID].

    To achieve this you need to right it as:

    ISNULL([Book ID],-1) AS SubBookID

    As you see you do not need the IIF function anymore in this situation.

    Read more about ISNULL in T-SQL: https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017