OREPLACE issues between environments

Database
Enthusiast

OREPLACE issues between environments

Hello,

I have a query using OREPLACE that runs in one environment, but fails in another with:

Executed as Single statement.  Failed [7509 : HY000] Result Exceeded maximum length for UDF/XSP/UDM pm_edw_etl_load.oreplace2. 
Elapsed time = 00:00:00.437

STATEMENT 1: SEL failed.
sel OREPLACE('sel OM_ID FROM DB.TABLE1 where OM_ID IN (SELECT OM_ID DB.TABLE1
WHERE OM_ID_TYP_CD = FILTER_OM_ID
AND AGR_JUS_STT_PRV IN (STATE_LIST)
AND LINE_OF_BUSINESS IN (LOB_LIST)
AND BRAND_CD IN (BRAND_CD_LIST)
AND TCG_SRC_CD IN (TCG_SRC_LIST)
AND PROD_LINE_CD IN (PROD_LINE_LIST)
','STATE_LIST', '''' ||
(CASE WHEN STATE_LIST = 'ALL' THEN TRIM('AK'',''AL'',''AZ'',''AR'',''CA'',''CO'',''CT'',''DE'',''FL'',''GA'',''HI'',''ID'',''IL'',''IN'',''IA'',''KS'',''KY'',''LA'',''ME'',''MD'',''MA'',''MI'',''MN'',''MS'',''MO'',''MT'',''NE'',''NV'',''NH'',''NJ'',''NM'',''NY'',''NC'',''ND'',''OH'',''OK'',''OR'',''PA'',''RI'',''SC'',''SD'',''TN'',''TX'',''UT'',''VT'',''VA'',''WA'',''WV'',''WI'',''WY'',''~')
WHEN STATE_LIST = 'NONE' THEN ''
ELSE OREPLACE(STATE_LIST, ',', ''',''') END) || '''')

from DB.OTHER_TABLE;

The data in both environments is identical, as are the table definitions.

Few questions here:

1. There a at least 2 versions of OREPLACE installed in both environments, and when it fails in the second environment it shows OREPLACE2 as the failing UDF. How can I find what is being executed successfully in the first environment?

2. Is there a mechanism that aliases the function name, and how can i find what is aliased to what?

3. By all accounts the first environment should fail as well, as the two versions I see both only accept CHAR(512), and my query exceeds that in places. Not really a question, but seems to indicate what I am looking at is not what is executing.

1 REPLY
Enthusiast

Re: OREPLACE issues between environments

just realized i put this in the wrong sub-forum, apologies, and feel free to move if warranted.

Thanks