Categories
- All Categories
- 89 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.1K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 51 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations Gallery
- 2 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Retrieve specific length of characters from a string
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.
Answers
-
Hi Nalsoft Pvt Ltd ,
Try with LEFT() once with above
0 -
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
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
0