I am passing an activity class to a Query Database Smart Service and am receivin

matthewz433 over 9 years ago

I am passing an activity class to a Query Database Smart Service and am receiving the following error: There was a problem executing the SQL query: SQL Message: ORA-01722: invalid number. I am using the “IN” operator to update multiple values. When I hard code the values for 'ac!IDs' the query executes perfectly. However when I try to pass in values from an Integer PV of type multiple I receive this error message. I have searched the forum and have tried a few different solutions including joinArray() that converts the integer of type multiple into a single text string.
Current Configuration -
SQL Statement: UPDATE PVCR_CASE SET STATUS = ac!status WHERE CASEID IN(ac!IDs)
ac!IDs: text that is not of type multiple. Formula: =joinarray(pv!selectedCaseIDs ,",")
The field we are searching in the database is of type Integer.
Any assistance would be much appreciated!

Discussion posts and replies are publicly visible

Parents

0 mahipalc over 9 years ago

This should work :

UPDATE PVCR_CASE SET STATUS = ac!status WHERE INSTR(','||ac!IDs||',', ','||PVCR_CASE.CASEID||',')=0
where ac!IDs is TEXT, single type. And ac!IDs is configured as =joinarray(pv!temp,",")
where temp is a process variable of number, multiple type (for example {100,200})
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 mahipalc over 9 years ago

This should work :

UPDATE PVCR_CASE SET STATUS = ac!status WHERE INSTR(','||ac!IDs||',', ','||PVCR_CASE.CASEID||',')=0
where ac!IDs is TEXT, single type. And ac!IDs is configured as =joinarray(pv!temp,",")
where temp is a process variable of number, multiple type (for example {100,200})
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

0 harshav over 2 years ago in reply to mahipalc

Hi mahipalc mahipalc we tried this but i am still getting an error saying truncated double error could you please help on this.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel