CODEWITHSUNDEEP
sqlms-accessiif-function
Pablo Rodriguez
Pablo Rodriguez

Reputation: 27

How to concatenate IIFs in SQL Access

What I am trying to do is to create a table where I have two columns, the second one being a clustering of the Variable1, to categorize it according to its values; if < 0.1, then I want to show "0-10", if the value is between 0.1 and 0.2, then show, "11 - 20", if other, then show "21 - 100". When I run the code below, "Missing syntax operator error" appears.

SELECT Variable2_name,
       IIF(Variable1_name< 0.1,
           "0 - 10",
           IIF(Variable1_name >= 0.1 AND Variable1_name < 0.2,
               "11 - 20",
               "21 - 100")
       ) AS Bucket
FROM Table
GROUP BY Variable2_name,
         IIF(Variable1_name < 0.1,
             "0 - 10",
             IIF(Variable1_name < 0.1,
                 "0 - 10",
                 IIF(Variable1_name >= 0.1 AND Variable1_name < 0.2,
                     "11 - 20",
                     "21 - 100")
             ),
ORDER BY Variable2_name

The problems has to be within the IIF clause, since when I had a simple IIF clause it worked properly. What is wrong with the syntax? What is the way to write concatenate an IIF clause within another IIF clause, and how do you have to include that in the GROUP BY clause?

Thanks a lot!

Thanks a lot

Upvotes: 1

Views: 237

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You have an obvious syntax error in the code (two iif()s in the select and three in the group by). In general, switch is easier to work with than nested iif()s. It works a lot like case in other databases:

SELECT Variable2_name,
       SWITCH(Variable1_name < 0.1, "0 - 10",
              Variable1_name < 0.2, "11 - 20",
              "21 - 100"
             ) AS Bucket
FROM Table
GROUP BY Variable2_name,
         SWITCH(Variable1_name < 0.1, "0 - 10",
                Variable1_name < 0.2, "11 - 20",
                "21 - 100"
               )
ORDER BY Variable2_name;

Note: Because the logic it iterative through the SWITCH(), you don't need two parts for the second condition. This also reduces the chance of errors.

Second, you are not using any GROUP BY functions, so you could further simply using SELECT DISTINCT:

SELECT DISTINCT Variable2_name,
       SWITCH(Variable1_name < 0.1, "0 - 10",
              Variable1_name < 0.2, "11 - 20",
              "21 - 100"
             ) AS Bucket
FROM Table
ORDER BY Variable2_name;

And, the DISTINCT is not necessary if you know that the values are already distinct.

Upvotes: 2

Gustav
Gustav

Reputation: 55921

Further to your malformed cut-and-paste, you can reduce it slightly:

SELECT Variable2_name,
       IIF(Variable1_name < 0.1,
           "0 - 10",
           IIF(Variable1_name < 0.2,
               "11 - 20",
               "21 - 100")
       ) AS Bucket
FROM Table
GROUP BY Variable2_name,
       IIF(Variable1_name < 0.1,
           "0 - 10",
           IIF(Variable1_name < 0.2,
               "11 - 20",
               "21 - 100")
       )
ORDER BY Variable2_name

Upvotes: 1

Related Questions