Hi All, anyone have experience on update multiple row on data store using Query

heriz over 9 years ago

Hi All, anyone have experience on update multiple row on data store using Query Database smart service?
Below is my SQL Statement :
UPDATE `Appian`.`einregcustomer` SET `flagadd` = '1' WHERE `einregcustomer`.`idcust` in (ac!cust)

ac!cust contain multiple value like 8,9,10 where user selected in paging grid on previous user input task.
With statement above, only the first row success updated. The next rows not updated.
Thanks before for your kindly response.

...

Discussion posts and replies are publicly visible

0 Eduardo Fuentes
Appian Employee
over 9 years ago

When configuring this node click on the Data tab and make sure the ac!cust has the "Multiple" checkbox enabled.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 heriz over 9 years ago

Hi Eduardo, I got this error when I change to multiple :
There was a problem executing the SQL query. SQL Message : [Ljava.lang.String; cannot be cast to java.lang.String.
Do I missed something?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Eduardo Fuentes
Appian Employee
over 9 years ago

I am assuming in the data tab this acp is mapped to a pv, correct? is that PV of type multiple too?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 heriz over 9 years ago

Hi Eduardo, yes its mapped to PV and I had set it to multiple too.
I attach the screenshot below, and the query statement still same :
UPDATE `Appian`.`einregcustomer` SET `flagadd` = '1' WHERE `einregcustomer`.`idcust` in (ac!cust).

On the user input task, I only show paging grid for user to select cust, then I save the selected cust Id to pv!selectedCust.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Eduardo Fuentes
Appian Employee
over 9 years ago

Can you try this:

1. Make the acp NON-multiple (uncheck the "Multiple" option)
2. Instead of mapping the acp to pv!selectedCust map it like this:

=joinarray(pv!selectedCust ,",")
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 heriz over 9 years ago

Hi Eduardo,
Yes! Its works now. Thanks a lot for your help.
Regards
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Eduardo Fuentes
Appian Employee
over 9 years ago

This is required because the SQL syntax for the IN requires a string separated by comas, this function is basically converting your array into a single text with comas separating each value.

I am glad is working now.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel