I have this querydelete 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 messageA) This is all on a single rowB) This is a single queryWhat's the solution to this?
Discussion posts and replies are publicly visible
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.
The question I have is "Why in the world do I need to send the full record for each record that I want to delete?"Let's say I have a table with 20 fields, and 100 records where I want to delete only 10 records.20 x 10 = 200 pieces data to save in the users' web browser and then send that to the web server.On the other hand, I want to delete the same 10 records, if I only capture the primary key then I only have to collect 10 pieces of data and send that to the web server.One of these two methods seems to use much more computer power and is not as efficient as the other.
What browser and web server are you talking about? Process logic is executed on the server only.
And why do you want to load 200 pieces of data? Just get the IDs and put them into that node.
This is low-code with Appian! Let simple things stay simple. There is no reason to make things more complicated just because other languages or environments require it.
petel0001 said:"Why in the world do I need to send the full record for each record that I want to delete?"
Where does this idea come from? The Delete from DSE node only takes identifiers, so if you're actually deleting rows, this should never be a concern. It would literally require exactly as much processing power as you're already attempting to use above.
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.