I built a view in which I'm using the following code to pull only the Last name from a field that is formatted 'LastName,FirstName':
VALUE(RTRIM(SUBSTR(A.PREVIOUS_NAMES,1,LOCATE(',', A.PREVIOUS_NAMES)-1)), '')
AS "PREVIOUS_NAME",
This view worked fine as I was working in our sandbox environment, but as I moved it up to test, and try to open the view in DBVisualizer, the data results provide the Error:
THE SECOND OR THIRD ARGUMENT OF THE SUBSTR OR SUBSTRING FUNCTION IS OUT OF
RANGE. SQLCODE=-138, SQLSTATE=22011, DRIVER=4.22.29
The sandbox has just under 11k records, where the test and prod systems are closer to 1.2 million, so I'm guessing this is a performance issue as a result of complexity of that piece of the code. The view still appears to have been created, I can query it and return a specific result/set of results, I guess I'm concerned on if it is usable for a large query, or if performance really is a concern in this case?
Is there a better way I can parse that last name without having to use the substring on a locate function?
Thanks for the help!
