When the IIF() Function is Your Friend

Why is this function your friend?

Well because it is flexible, allowing you to choose between two alternatives, which can be simple values or even other functions, including nested IIF functions. The IIF function is defined as follows.

IIF(condition, first alternative, second alternative)
The IIF function returns the value of the second parameter if the first expression evaluates as true, otherwise returns the third. e.g.

IIF(service_total_score > total_score, service_total_score, total_score) > 20 will select those objects with the maximum of the service_total_score and the total_score being greater than 20

Take the case where you want to generate a table using a SELECT statement. But because in a SELECT query you cannot use a WHERE statement more that once then we have to look to another function and this is where our friend IIF() can help us. Take for example if you wanted to create a table showing catchment ID on the left hand most column followed by columns for length of structural grade 4 and length of grade 5 pipes. The following SQL returns and error:

SELECT doesn’t like more that one WHERE clause

SELECT doesn’t like more that one WHERE clause

So we can try the following instead:

Now using our friend IIF()

Now using our friend IIF()

SELECT
SUM(IIF(condition_score=5, length, 0))/1000 AS ‘Length of CG4 (km)’,
SUM(IIF(condition_score=4, length, 0))/1000 AS ‘Length of CG5 (km)’,
SUM(IIF(condition_score>=0, length, 0))/1000 AS ‘Length Surveyed (km)’
GROUP BY drainage_code AS ‘Catchment’

And now what’s generated is the table required.

Table Created

Table Created

“Want to unleash the full power of SQL in InfoNet? Get training from an expert. See the Innovyze website for our training schedules.“

Share this post!

    About Idris Nujjoo

    Idris is the Product Sector Leader for InfoNet and InfoMaster and has been working for Innovyze (and its earlier incarnations) since 2001 and specifically working on InfoNet and Asset Management for the past decade.
    This entry was posted in InfoNet. Bookmark the permalink.