Confused about WHILE, IF

Clients often get confused about WHILE, IF etc. It is quite common for those unfamiliar with SQL often try to use IF/ELSE/WHILE when there is no need and normal SQL commands are more appropriate.

The important thing is to understand is that they can only be used to control the overall flow of the SQL query, they cannot be used to make decisions for individual objects.

So for example, when you write

SET user_number_1 = 5

That query is automatically applied to all objects of the specified type.

If you want to do something different depending on a condition, then you can use an IF/ELSE

IF ( condition = true)
SET user_number_1 = 5
SET user_number_1 = 6

Note that the condition can only decide which SET statement to execute (for all objects in each case), it cannot decide which value to use on an object by object basis. For this reason, no object fields can be used in IF (or WHILE) statements. Similarly, you cannot use object based variables in these statement (as they are treated in the same way as fields). You can use SCALAR variables and the other types (See Other Variables in Section 9 of the SQL technical note in the Help).

It is also worth noting, that the same effect as the example can easily be obtained without any IF/ELSE usage. E.g.:

SET user_number_1 = IIF(condition = true, 5, 6)

…which is simpler anyway.

I hope this helps to explain things.

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, InfoWorks ICM. Bookmark the permalink.