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:
So we can try the following instead:
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.
“Want to unleash the full power of SQL in InfoNet? Get training from an expert. See the Innovyze website for our training schedules.“