Case and Iif - Page 2

Now we'll do something similar but not quite the same in SQL rather than VB. This is so common on our pages that we've separated it out as a function, which is used repeatedly. For the small example that follows, it doesn't need to be a separate function, but you can imagine that if the function is called seven or eight times for a single recordset then it's a great help in keeping the SQL statement down to a reasonable size.

Here's the function:

<%
  Function DoCaseM(Vname)
  DoCaseM = "sum(Case when FinPeriod = '" + FCEB__varFP
    + "' then
  " & Vname & " else 0 end) as Sum" & Vname
  end function
  %>

[The colored lines above are one line. They have been split for formatting purposes.]

And here's the SQL statement from the recordset that uses it:

REF.Source= "SELECT DoHCode, " + DoCaseM("MCnt") + ",
  sum(MCnt) as sumYCnt FROM epscons.EPS_OP_REF group by
    DoHCode order by DoHCode"

[The colored lines above are one line. They have been split for formatting purposes.]

The recordset is used to create three columns. The first is just a list of codes. It's the other two that are more interesting. One needs to be filtered by the variable FCEB__varFP (this corresponds to a Financial Reporting Period) but the other doesn't, so we can't include the filter in the regular SQL. It has to be included in the SQL for the second column only, and that's what our DoCaseM function accomplishes.

There's one big drawback with this technique, which is that "Case" is recognized in some versions of SQL, but others need "iif". This is tricky because in our real world project our working database is Sybase (fine with Case) but our test database used for checking that the pages work before they are released is in Access (so requires Iif). As a result, we have to do a big global change before a page is mounted on the server. Slightly troublesome, but worthwhile in the long run.

The equivalent Iif function is:

<%
  Function DoCaseM(Vname)
  DoCaseM = "sum(iif(FinPeriod = '" + FCEB__varFP + "'," &
  Vname & ",0)) as Sum" & Vname
  end function
  %>

Hand-Coded SQL for UltraDev ASP Pages
Debugging and Error Messages - Page 3