User bio
404 bio not found
Cambridge,MA
Member since Dec 9, 2015
Posts:
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.
Steve, I've conveyed your message about Class Queries since the first time you mentioned it to me, oh so long ago. However, if you wanted to use Class Queries for Tim's example, you'd have to write 17 different queries, and call the right one based on the input you got. Every feature has its uses.