0

I'm currently struggling to get the following query to work:

UPDATE HT_USERS
SET HT_USERS.ZONE = 1
WHERE INSTR(:P8_MEM, USER_ID) > 0;

where :P8_MEM is a page item in the apex environment. The page item is a check box group which contains the USER_IDs of every employee selected by the user formatted in a single colon-delimited string. From my understanding, INSTR should return a value higher than zero if the string is contained in the page item and zero if not.

I already tried to hard code and replace the page item, which resulted in the expected behaviour of updating the HT_USERS table to contain the foreign key ZONE. I have another example of a check box working with a INSTR to check for certain numbers saved as characters. Converting either input to instr to varchar2 does not yield any different results.

Am I doing something wrong here?

Thanks for the feedback and have a great day!

emilyantosch
  • 1
  • Where exactly are you running that UPDATE statement? Is it a process? Did you try to create a SUBMIT button and let the process run when that button is being pressed? Because, SUBMIT will put :P8_MEM item's value into session state. The way you described it, it looks as if that didn't happen so Apex doesn't "see" what's in that item. Check it by running the page and viewing Session info (in the developer's toolbar at the bottom of the page). – Littlefoot Nov 10 '22 at 07:16
  • Thank you for your comment! This really helped. You were right, I did not have the query to update the table in the right place as it was probably executed before the submit. Now I run it in a process after the submit and it works fine! – emilyantosch Nov 10 '22 at 07:54
  • Great, I'm glad you made it work. – Littlefoot Nov 10 '22 at 07:55

1 Answers1

0

Apart from the problem of where in/from the page your query is running (that was answered in comments), your query is wrong because it is likely to update the wrong users as you never check that a complete term is matched in the delimited list.

UPDATE HT_USERS
SET HT_USERS.ZONE = 1
WHERE INSTR(:P8_MEM, USER_ID) > 0;

If :P8_MEM is 13;15 then this will match USER_IDs with the values 1, 3, 5, 13 and 15 as they are all sub-string matches but only 13 and 15 are complete terms and should be matched.

What you need to do is check for a complete term with the surrounding delimiters:

UPDATE HT_USERS
SET HT_USERS.ZONE = 1
WHERE INSTR(';' || :P8_MEM || ';', ';' || USER_ID || ';') > 0;

or

UPDATE HT_USERS
SET HT_USERS.ZONE = 1
WHERE ';' || :P8_MEM || ';' LIKE '%;' || USER_ID || ';%';
MT0
  • 143,790
  • 11
  • 59
  • 117