Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Retrieve specific length of characters from a string

Received Response
92
Views
2
Comments

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

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach

    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