...and it makes sense that using $listbuild to add at the end would be better than the other two options, which are general purpose ("find the position in the list and put something there").

As @Yaron Munz and @Alexander Koblov correctly pointed out, you can use Embedded SQL or Dynamic SQL. You can also use Class Queries.

Using Embedded SQL, host variables in IRIS (:varname) can be inputs or outputs, just like host variables in SQL Server and Oracle, but without any DECLARE statement. Host variables used as inputs are automatically sanitized. Examples:

WHERE amount > :var1
INSERT INTO ... VALUES (:var1, :var2, :var3)
SELECT ... INTO :var1, :var2 FROM ...

Dynamic SQL allows ? placeholders for inputs only instead of host variables, also automatically sanitized. Examples:

WHERE amount > ?
INSERT INTO ... VALUES (?, ?, ?)

Dynamic SQL returns output values in the result set object it returns, which you can access and copy into variables. Examples:

set resultSet = ##class(%SQL.Statement).%ExecDirect(, sql, values for any placeholders)
while resultSet.%Next() {
    set var1 = resultSet.column1
    set var2= resultSet.column2
}

Class queries use host variables (:varname) for inputs (like Embedded SQL), and return output values in the result set object (like Dynamic SQL).

I think the other commenters clarified it all, but I thought I'd add a little more.

Try this variant, with the variable x intentionally undefined.

write (1=0) && (x = 0)
0

Since 1=0 is 0, you don't get an <UNDEFINED> for x, since the right-hand (x=0) expression is ignored, and the entire statement is 0. Now try this:

write 1=0 && x=0
1

which, as @Roger Merchberger showed, is really

write (((1=0) && x) = 0)

Since 1=0 is 0, you again don't get an <UNDEFINED> for x, since the x expression (only) is ignored, and the ((1=0) && x) expression is 0. Finally, 0=0 is 1.

As others in this thread have said, custom audit events from applications are added to the Audit Log using a call to $system.Security.Audit(). The events must be defined ahead of time in the Portal, using Configure User Events. All events (system and custom) have a 3-part name: Source, Type, and Name. I think of these parts as Main Category, Subcategory, and Name.

The person(s) defining the custom events can choose all 3 parts of the name, in order to categorize all the custom events they're defining. So you could use Source = ABC, Type = DEF, and Name = XYZ. The names are totally up to you. Once defined, the event gets added by some code calling $system.Security.Audit("ABC", "DEF", "XYZ").
 

Thanks Robert! And there's another one!

• $LISTBUILD – Builds a list from elements or extracts elements from a list. 

I have a comment on the sentence "I don't want to use Xecute because I am in need of really high performance code."

I have always thought of Xecute and @ (indirection) as similar in performance characteristics. But I don't know what the current reality is. Is it possible that omer was told that Xecute is slow and indirection wasn't mentioned?

Here are some quotes from the docs. I don't see anything here that indicates Xecute is slower or faster than indirection. Maybe someone else on this thread has more detailed knowledge on the difference if any.

• The execution time for code called within XECUTE can be slower than the execution time for the same code encountered in the body of a routine. This is because InterSystems IRIS compiles source code that is specified with the XECUTE command or that is contained in a referenced global variable each time it processes the XECUTE.
• You should use indirection only in those cases where it offers a clear advantage. Indirection can have an impact on performance because InterSystems IRIS performs the required evaluation at runtime, rather than during the compile phase.
• You can always duplicate the effect of indirection by other means, such as by using the XECUTE command.

Common Table Expressions are now supported starting with v2024.1: https://docs.intersystems.com/irisforhealth20241/csp/docbook/DocBook.UI.... 

I never encountered this issue. I assume it's because AutoSave is off by default, and Steve and others that have commented turned it on. I noticed that there IS a setting called "Auto Save When No Errors."

Very nice. Please remove "not" from "...its associated searches does not open up a world of opportunities..."

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.

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 🧐). Update 5/7/24: I have verified that in v2024.1, Runtime Plan Choice generates different good plans based on presence or absence of values for ? placeholders!
• 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!

For those that posted "I never knew that! Wow!" about the aliases: didn't you read this 2 years ago?
😞

Some more thoughts on this:

• If the 3rd party system claims that they're not starting a transaction, but you have evidence that a transaction is starting, it would be good to try to work together to get to the bottom of that. Of course that's up to you. It's best to get the WRC involved.
• Transactions are an application thing, not a user-oriented thing. There is no way to say "prevent user X or role Y from starting a transaction."
• There is a potential reason for the 3rd party system to be using transactions even when simply reading data with SELECT, known as Isolation Level. Read about it here. To establish Isolation Level, you can use SET TRANSACTION (which doesn't start a transaction), or START TRANSACTION (which obviously does start a transaction). By default, a SELECT shows you all the matching rows, even rows that are uncommitted (changes to those rows could be rolled back). ISOLATION LEVEL READ COMMITTED is used when you want to guarantee that a SELECT shows you only committed data, although this may result in the SELECT failing to complete when it reaches a row that has not been committed yet. So maybe the 3rd party system is using START TRANSACTION for that. Maybe they could switch to SET TRANSACTION instead. Edit: Unfortunately, IRIS does not provide the ability to GRANT or REVOKE the ability to use SET/START TRANSACTION.
• You wrote "If they have a process that is starting a transaction, i want it to fail/return an error to them." But wouldn't that prevent any of their queries from running? In other words, since the 3rd party system always starts a transaction, and that behavior can't be turned off, IRIS would return an error every time, and the query wouldn't run.

I edited this post and made some corrections below.

I think the Community needs more information in order to help you. Data for %Persistent classes is stored in globals. Data for %SerialObject classes is also stored in globals. Properties of %Persistent classes can reference %SerialObject classes, and when the %Persistent data is saved, the referenced %SerialObject data is also stored in globals.

The decision about whether to create %Persistent or %SerialObject classes for storing data is an object modeling decision, unrelated to the need to purge data on some schedule. Changing classes from %Persistent to %SerialObject (or vice versa) for an existing application is a major design change which would probably require plenty of code rewrites. I don't think anyone would do that work just for the sake of purging. There must be something else going on.

Semi-educated guess: Maybe when you say "purging" you're talking about purging messages in interoperability productions. These messages have message headers, message bodies, and the bodies may have properties that reference %Persistent or %SerialObject objects. When you purge those messages, you have the option to purge message bodies, but if the bodies reference %Persistent objects, the referenced objects are not purged. Maybe that's what you're seeing, and maybe that's why you're thinking of switching storage definitions. There is certainly a way to make sure that any referenced objects get purged when messages are purged, documented (briefly) here. The WRC can also offer assistance.

Please tell us if my semi-educated guess is correct, or if not, please give us more information. Happy New Year!