"You should enter only a single query in a row." ???

Question

I have this query delete from PLSM_note where noteId in (fn!substitute(fn!joinarray(ac!selectedRecordIds, ";"),";",",")) as shown in the below image. 
 This query should translate to: 
 delete from PLSM_note where noteId in (1,2,4,50) However, I'm getting this message A) This is all on a single row B) This is a single query What's the solution to this?

Mike Schmitt · Answer

The "Delete from DSE" setup is super simple, too. 
 Side note: it's advised to only delete DB rows when you're *really really sure* you want them GONE. Otherwise it's a strongly-suggested best practice to simply include a flag on the row along the lines of "is active" (which you'd set to "false") or "is deleted" (which you'd set to "true") and write the modified row back to the DB. These flags would then be able to be reflected in default-condition queries in the future to avoid showing them on the front end, other than for admin / auditing reasons.

Mike Schmitt · Answer

And if you're going with the approach of updating the "is active" flag (etc) on a row, and deactivating them rather than deleting them - then yes, the lazy / most common approach would see the entire rows queried, modified, and written back - and really this would never be an issue unless the load of such transactions is expected to be heavy consistently. 
 If so, then it's fairly easy to create an alternative CDT and accompanying DSE that contains JUST the primary key and the fields to update (i.e., a CDT containing the primary key ID and the "isActive" flag, though usually I'd also include a "modified by user" and "modified date" field in this), which when re-written, only overwrites the fields contained in that CDT, and leaves the rest of the data in the row alone.

Mike Schmitt · Answer

Why in the world are you using the Query Database node to do this?! Please please please just use the "Delete from DSE" node. The QDB node should be used only in extreme circumstances, and even then I always recommend trying all other approaches first . 
 Anyway the actual answer to your question: the SQL statement consumed by this node isn't expressionable and there's no reason to think it can read Appian Expression code - the fact that it can swap in "ac!" values is the only exception to this and is, what I consider to be, a bare workaround. Still I would never, ever attempt what you're attempting in your screenshot in this node.

"You should enter only a single query in a row." ???

Top Replies