User bio
404 bio not found
Cambridge,MA
Member since Dec 9, 2015
Replies:

Oh, and one more little tidbit about the 2nd argument to %ExecDirect(), the one containing the SQL text. Instead of being a single long string, it can be an array of strings, passed by reference with a leading dot (not variadic). So the first few lines of GetTransactions() could look like the example below. This approach has the nice benefit of adding spaces between each line; notice I removed the extra spaces at the end of each line of SQL.

    set sql = 1
    set sql(1) = "select Product->Name, Outlet->City, AmountOfSale, UnitsSold "_
        "from HoleFoods.SalesTransaction where Actual = 1"
    if (product '= "") {
        set sql($increment(sql)) = "and Product = ?"
        set args($increment(args)) = product
    }
    if (channel '= "") {
        set sql($increment(sql)) = "and Channel %INLIST ?"
        set args($increment(args)) = channel
    // ...and so on...and eventually...
    set result = ##class(%SQL.Statement).%ExecDirect(, .sql, args...)

It's a leading dot and trailing dots festival.

A few thoughts on this lovely post:

  • I think "Bad Solution #1" is really "Terrible, Awful, Never Ever Do This And I'm Not Kidding Solution #1."
  • I think "Bad Solution #2: Spaghetti Code" is really "OK Solution #1: Complex ObjectScript".
  • I think the alternative solution with the multiple "OR ? is null" statements could be "OK Solution #2: Complex SQL" but only if Runtime Plan Choice is available in your version and only if it allows the optimizer to pick a good plan in all cases (still to be verified by someone 🧐).
  • Performance options for the WHERE clause on channels. I haven't tested which of these options is better as the list of channels gets larger (although I don't think it would get really large).
    • Supplying a $listbuild of the channels and using %INLIST on that uses $listfind to test the condition for each row.
    • Looping through the $listbuild (as in Bad Solution #1), using either the "not-so-good $listlength style" or the "better $listnext style," and building a series of ORs uses $data on the subscripts of an array to test the condition for each row. Looping through the $listbuild and building a simpler IN instead of the series of ORs also uses $data on an array.
  • I think "Variadic" is my new favorite word!
Open Exchange applications:
Certifications & Credly badges:
Joel has no Certifications & Credly badges yet.
Global Masters badges:
Followers:
Following:
Joel has not followed anybody yet.