Optimize access query with nested IIF's

Is there a better way to write the following in my where开发者_StackOverflow clause?

WHERE (IIf([GrpOrder]=3,IIf([LabelText]="Totals",True,False),True)) =True))

Thanks,

Scott

I assume your code contains typos (unblanaced parentheses) and should in fact read:

WHERE IIf([GrpOrder]=3,IIf([LabelText]="Totals",True,False),True) = true

From a SQL code perspective there are actually nine cases to consider because of SQL's three value logic with NULL:

GrpOrder = 3
GrpOrder <> 3
GrpOrder IS NULL

LabelText = 'Totals'
LabelText <> 'Totals'
LabelText IS NULL

In combination there are nine cases e.g. test data and results:

OP_result | GrpOrder | LabelText  
----------------------------------
     TRUE |       55 | 'Totals'
     TRUE |       55 | 'Tallies'
     TRUE |       55 | <NULL>
     TRUE |        3 | 'Totals'
    FALSE |        3 | 'Tallies'
    FALSE |        3 | <NULL>
     TRUE |   <NULL> | 'Totals'
     TRUE |   <NULL> | 'Tallies'
     TRUE |   <NULL> | <NULL>

The safest approach would be to write out a series of OR clauses, explcitly handling NULL for both column for each OR clause. However, that is very long winded it would be better to taget those two cases that return FALSE. And this is where most folk (including me!) run into problems with NULL: it's just too counter-intuitive!

For example, it is tempting to write this:

(GrpOrder = 3 AND LabelText IS NULL)
OR
(GrpOrder = 3 AND LabelText <> 'Totals')

then 'flip' its value using NOT:

NOT (
     (GrpOrder = 3 AND LabelText IS NULL)
     OR
     (GrpOrder = 3 AND LabelText <> 'Totals')
    )

However, in doing so NULL sneaks into the resultset:

OP_result | attempt_1 | GrpOrder | LabelText  
---------------------------------------------
     TRUE |      TRUE |       55 | 'Totals'
     TRUE |      TRUE |       55 | 'Tallies'
     TRUE |      TRUE |       55 | <NULL>
     TRUE |      TRUE |        3 | 'Totals'
    FALSE |     FALSE |        3 | 'Tallies'
    FALSE |     FALSE |        3 | <NULL>
     TRUE |      TRUE |   <NULL> | 'Totals'
     TRUE |    <NULL> |   <NULL> | 'Tallies'
     TRUE |    <NULL> |   <NULL> | <NULL>

So we need to explicitly handle more cases than it might appear at first glance.

The simplest predicate I could come up with that gives the desired result in Access:

NOT
(
 (LabelText <> 'Totals' OR LabelText IS NULL)
 AND GrpOrder = 3 
 AND GrpOrder IS NOT NULL
)

[...which is so odd to read I wonder whether the OP's code is yielding the desired result in the first place.]

The main lessons to learn:

• NULL in SQL should be avoided: it is counter-intuitive even causes bugs even by very experienced SQL coders.
• Always post your schema (e.g. CREATE TABLE SQL DDL...) and sample data (... e.g. INSERT INTO SQL DML...) with expected results (... or use words and pictures if you must ;) because if your columns are marked as NOT NULL then the answer is very much simpler! :)

@Yanir Kleiman comments:

GrpOrder can't be 3 and NULL at the same time, so checking it is not null in this case is redundant

One could be forgiven for thinking so. But this is Access :) We have excellent specs for SQL products that claim compliance with the SQL Standards. Access claims no such compliance and the documentation the Access Team have provided is of a particularly low quality.

Rather, in Access-land, for something to be true, you have to actually test it!

When I remove the predicate

AND GrpOrder IS NOT NULL

nulls appear in the resultset. While it feels like this 'defies logic', bear in mind that SQL's three value logic is only defined in a spec to which Access claims no compliance. If the Access Team don't tell us how the product is supposed to work, how can we tell whether the above is a bug or a feature? And even if we could convince them it is a bug, would they fix it?

Below I provide VBA code to reproduce the issue: just copy+paste into any VBA module, no references need to be set. It creates a new .mdb in the temp folder, then creates the table and test data. Access need not be installed on the machine e.g. use Excel's VBA editor.

The messagebox shows shows the resultset when the above predicate is included and removed respectively. In addition to the two table columns, two calculated columns show with values -1 (TRUE), 0 (FALSE) and NULL and the leftmost one is the OP's:

Sub AccessStrangeLogic()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    With .ActiveConnection

      Dim Sql As String
      Sql = _
      "CREATE TABLE GrpOrders" & vbCr & _
      "(" & vbCr & _
      " GrpOrder INTEGER," & vbCr & _
      " LabelText NVARCHAR(10)" & vbCr & _
      ");"
      .Execute Sql

      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (55, 'Totals');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (55, 'Tallies');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (55, NULL);"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (3, 'Totals');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (3, 'Tallies');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (3, NULL);"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (NULL, 'Totals');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (NULL, 'Tallies');"
      .Execute Sql
      Sql = _
      "INSERT INTO GrpOrders (GrpOrder, LabelText)" & _
      " VALUES (NULL, NULL);"
      .Execute Sql

      ' Include "AND GrpOrder IS NOT NULL"
      Sql = _
      "SELECT *, " & vbCr & _
      "       IIf([GrpOrder]=3,IIf([LabelText]=""Totals"",True,False),True) = true AS OP_result, " & vbCr & _
      "       NOT" & vbCr & _
      "       (" & vbCr & _
      "        (LabelText <> 'Totals' OR LabelText IS NULL)" & vbCr & _
      "        AND GrpOrder = 3 " & vbCr & _
      "        AND GrpOrder IS NOT NULL" & vbCr & "       )" & vbCr & _
      "  FROM GrpOrders" & vbCr & _
      " ORDER " & vbCr & _
      "    BY GrpOrder DESC, LabelText DESC;"

      Dim rs
      Set rs = .Execute(Sql)

      ' Remove "AND GrpOrder IS NOT NULL"
      Sql = Replace$(Sql, "AND GrpOrder IS NOT NULL", "")

      Dim rs2
      Set rs2 = .Execute(Sql)

      MsgBox _
          "Include 'AND GrpOrder IS NOT NULL':" & vbCr & _
          rs.GetString(, , vbTab, vbCr, "<NULL>") & vbCr & _
          "remove 'AND GrpOrder IS NOT NULL':" & vbCr & _
          rs2.GetString(, , vbTab, vbCr, "<NULL>")


    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

First of all the second IIF is redundant - "IIF(X, True, False)" can always be replaced by "X".

Apart from that, the logic of the select is "where GrpOrder = 3 and LabelText="Totals", OR GrpOrder <> 3".

That is the same as saying "where LabelText="Totals" OR GrpOrder <> 3", hence:

WHERE [GrpOrder] <> 3 OR [LabelText]="Totals"

*I don't remember if access uses <> or != for inequality, so whichever works.

Edit:

We have 4 cases in total:

GrpOrder = 3 and LabelText = "Totals" => accept

GrpOrder = 3 and LabelText <> "Totals" => don't accept

GrpOrder <> 3 and LabelText = "Totals" => accept

GrpOrder <> 3 and LabelText <> "Totals" => accept

The only case we do not accept is when GrpOrder = 3 and LabelText<> "Totals", which is the same as saying we accept all rows where GrpOrder <> 3 (bottom two rows) or LabelText="Totals" (first and third row). Row 2 is the only one that is not accepted.

I don't want any records where GrpOrder=3 except where GrpOrder=3 and LabelText="Totals".

where GrpOrder <> 3 or (GrpOrder = 3 and LabelText="Totals")