Hi All,
In Oracle 12c i have the below string populated in a column called PREDICATE:
&TABLE_ALIAS.VALUE in
(
SELECT
PK1_VALUE
FROM
GL_SEG_VAL_HIER_RF
WHERE
TREE_STRUCTURE_CODE = 'GL_ACCT_FLEX'
AND TREE_CODE = 'Cost Centre UOB Ledger'
AND TREE_VERSION_ID =
(
SELECT
TREE_VERSION_ID
FROM
FND_TREE_VERSION_VL
WHERE
TREE_STRUCTURE_CODE = 'GL_ACCT_FLEX'
AND TREE_CODE = 'Cost Centre UOB Ledger'
AND STATUS = 'ACTIVE'
AND EFFECTIVE_START_DATE <= SYSDATE
AND EFFECTIVE_END_DATE >= SYSDATE
AND ROWNUM = 1
)
AND ANCESTOR_PK1_VALUE ='AA'
)
I would like to extract the value which corresponds to the 'AND ANCESTOR_PK1_VALUE = 'AA'' i.e. AA.
i achieved the above using combination of SQL functions, but it doesn't look so stable( assuming that there could be a blank space before/after the '=' sign and that could change the instr function...
replace(replace(substr(i.predicate_val, instr(i.predicate_val, 'AND ANCESTOR_PK1_VALUE =', 1,1 )+24),''''),')')
I presume there could be a much better way of getting the AA value from the above string? Any ideas?
Thanks in advance,
Aleks