I've come across this in one of the sql scripts we have for one of our apps. I notice that it's used in various other places, but isn't it just checking for an existence of an item?
AND INSTR((SELECT (',' || REPLACE('OWN, JO', ' ', NULL) || ',') b FROM DUAL),
(',' || aao.AcctRoleCd || ',')) > 0
Where it's looking to see if 'OWN' or 'JO' is in aao.AcctRoleCd. If it is then INSTR would result in its index in the string, so it'll be greater than one. So the AND clause would be true.
Isn't this poor to check if an item exists like this? Would something more of the lines of an IN clause be better?
AND aao.AcctRoleCd IN ('OWN', 'JO');
Almost:
'OWN, JO' is a text literal.REPLACE('OWN, JO', ' ', NULL) just strips the space from the string giving 'OWN,JO'.',' || 'OWN,JO' || ',' just concatenates commas to the start and end of the string giving ',OWN,JO,'.(SELECT ',OWN,JO,') b FROM DUAL) is redundant and you can just use the previous text literal.INSTR( ',OWN,JO,', (',' || aao.AcctRoleCd || ',') ) > 0 is looking for a comma at the start and end of the substring which equals aao.AcctRoleCd so could match either 'OWN', 'JO' or 'OWN,JO'.So you can replace it with:
AND aao.AcctRoleCd IN ( 'OWN', 'JO', 'OWN,JO' )
Now, it may be that 'OWN,JO' is not a match you are expecting (or may not even be a valid value) and you can strip it from the list but that is something you will need to determine.