Retrieve specific length of characters from a string

Question

In our case the string is a combination of segments concatenated with a '.'(Full Stop)-- Segment1.Segment2.Segment3...

How do i fetch Segment2, i tried using SUBSTRINGN and LOCATE and was able to retrieve data from the end of segment1 to end of the string.

Need to get MURAQQABAT from the string mentioned above.

Nathan CCC · Answer

Hi,

From you screenshot it looks like you are trying to do this using logical sql in an anaysis in otbi (not physical sql using a data set with one of the application database connections to the application tables and views HCM/FSCM/CRM in a data model for a report in OTBI). This makes a difference because it is a different language with different syntax for substring etc.

If logical this is your user guide

Oracle® Fusion Middleware

Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition

Release 12c (12.2.1.3.0)

E80604-01 August 2017

https://docs.oracle.com/middleware/12213/biee/BIESQ/toc.htm

but if I am wrong then if physical then this is your user guide

Oracle® Database

SQL Language Reference

12c Release 2 (12.2)

E83703-08 January 2023

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/#Oracle%C2%AE-Database

Is this chart of accounts? If so, If you are using the subject areas then you do not need to do this because Oracle has already done this for you.

If you have configured your chart of accounts as per the user guide then each of the individual segments already exists as its own column along with the 2 column with the concatenated codes and descriptions. So you do not at all need to do any string manipulation.

For example

select all 0 s_0
, "Account"."Concatenated Segments" as concatenated_code
, "Account"."Code Combination Description" as concatenanted_desc
, descriptor_idof("Balancing Segment"."Balancing Segment Description") as segment_1_code
, "Balancing Segment"."Balancing Segment Description" as segment_1_desc
FROM "General Ledger - Transactional Balances Real Time"
FETCH FIRST 3 ROWS ONLY

And if you look at the session log you will find that when the metadata repository database (RPD) converts your logical sql to physical sql it uses a database package function to get the individual segments from the concatenated code combination so again if you are doing this physical then you can use the out of the box function rather than need to use regular expressions or substring type functions etc.

select all t.code_combination_id
, (fnd_flex_xml_publisher_apis.process_kff_combination_1
(upper('FLEXFIELD'), upper('GL'), upper('GL#'), t.chart_of_accounts_id, null, t.code_combination_id, upper('ALL'), upper('Y')
, upper('VALUE'))) 
as concatenated_code
, (fnd_flex_xml_publisher_apis.process_kff_combination_1 
(upper('FLEXFIELD'), upper('GL'), upper('GL#'), t.chart_of_accounts_id, null, t.code_combination_id, upper('ALL'), upper('Y')
, upper('FULL_DESCRIPTION'))) 
as concatenated_desc
, t.segment1 
as segment1_code
, (fnd_flex_xml_publisher_apis.process_kff_combination_1 
(upper('FLEXFIELD'), upper('GL'), upper('GL#'), t.chart_of_accounts_id, null, t.code_combination_id, '1', upper('Y')
, upper('FULL_DESCRIPTION'))) 
as segment1_desc
-- https://docs.oracle.com/en/cloud/saas/financials/22d/oedmf/glcodecombinations-12960.html#glcodecombinations-12960
-- gl_code_combinations_pk code_combination_id
-- gl_code_combinations_u1 unique default code_combination_id
from gl_code_combinations t

Dimple-Nagesh-Support-Oracle · Answer

Hi  Nalsoft Pvt Ltd ,

Try with LEFT() once with above