ORACLE REGULAR EXPRESSION TO FIND STRING BETWEEN TWO STRINGS

Introduction: 

Below is the syntax for regular expression REGEXP_SUBSTR

REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )

Example:

The below expression can be used to obtain string between two string values using regular expression.

Lets suppose we have a string 'Hello world This is [Dinesh] - You are welcome to My Blog - Thanks for your support'.

I want to get string between string ('] -') and ('-') then use the below code

Query:

SELECT 

TRIM (REGEXP_SUBSTR ( 'Hello world This is [Dinesh] - You are welcome to My Blog. - Thanks for your support'  , ' \] - (.*?) \ - ',1,1,null,1))

FROM  DUAL;

Output:

You are welcome to My Blog.

Replace the strings ('] -') and ('-') with our own string in the expression.



        *?     Matches the preceding pattern zero or more occurrences.
        ( )     Used to group expressions as a sub expression.
         .      Matches any character except NULL. 

Instead of above format we can also use the below code

(REGEXP_SUBSTR('Hello world This is [Dinesh] - You are welcome to My Blog. - Thanks for your support' ,'\ - (.*?)\ - ',1,2,null,1))

This expression searches for the second pattern of the string '\ - (.*?)\ - ' .
Here (.*?)  acts like escape sequence.

ORACLE ANALYTICAL FUNCTIONS (WINDOWING FUNCTIONS)

Introduction :

Analytic functions(SUM() OVER, RANK,ROW_NUMBER etc) will be executed in a SQL query after all JOINS,WHERE,GROUP BY and HAVING clause except ORDER BY. These functions are the last set of operations performed on group of rows or window of rows. And most important point is that these wont need GROUP BY clause unlike normal aggregate functions.

• Analytical functions are also known as windowing functions.
• Analytical functions compute values based on group of rows and display value for each row.  
• Analytic functions are used to compute cumulative, moving, centered, and reporting aggregates.
  
  

  	Analytical Functions	Aggregate Functions
  Definition	Analytical functions compute aggregate value based on group of rows and return multiple rows for each group.	Aggregate Functions return single value for a set of rows.
  Syntax	Analytical_function([arguments]) over ({query partition clause}) [order by clause] ([windowing clause] )	Aggregate_function(column)
  Example	SELECT DEPT_ID,EMPNO,SAL,MAX(SAL) OVER () AS MAX_SALARY FROM EMPLOYEES; SELECT DEPT_ID,EMPNO,SAL,MAX(SAL) OVER (PARTITION BY DEPT_ID) AS MAX_SALARY FROM EMPLOYEES;	SELECT MAX(SAL) FROM EMPLOYEES; SELECT DEPT_ID,EMPNO,SAL,MAX(SAL)  AS MAX_SALARY FROM EMPLOYEES GROUP BY DEPT_ID,EMPNO,SAL;

  
  
  

  Analytical Function Syntax :

  1.       Arguments: Analytical Functions can take 0 to 3 arguments.

  2.       Over and Partition Clause:  These clauses are used to indicate that the list of columns on which the query gets computed. If we do not specify the partition clause then the query treats all the rows as single set and calculates average for all the employees irrespective of departments.

  3.       Example: Query to calculate average salary for department and display against each employee.                                                                                                                                                       SELECT EMPLOYEE, DEPT_ID, AVG (SAL) OVER PARTITION BY AS AVG_DEPT FROM EMPLOYEES.

  4.       Windowing Clause:  Some analytical functions allow window function. Ex: max, min etc.

  
  
  

  
  

  Analytical functions examples:

  -- Query to display department wise max salary using analytical function
  select empno,deptno,max(sal) over (partition by deptno order by empno) max_sal from emp ;
  

  

  
  Aggregate function examples:

  
  

  

      From the above 2 examples we can infer that 

• Analytical functions does not reduce the row count and will display the values against all the rows.
• Aggregate functions reduce the row count.

      Similarly we can  use the other Analytical functions.

      For more information please visit the oracle website : http://docs.oracle.com/cloud/latest/db112/SQLRF/functions004.htm#SQLRF06174

    

PROJECTS IN TALEND OPEN STUDIO

The highest physical structure for storing all types of data integration jobs, metadata etc.
We can create project after launching the Studio for the first time.
We can create any number of projects.

Steps to create a project.
1. Launch Talend Studio.
2. Create new project in the login window and enter project name.
3. Click create.

like this we can create as many projects as we want and we can choose between the projects when we login.

OBIEE QUERY FOR YESTERDAY'S DATE

• Use the below code to get yesterday's Date in obiee.
• Place this code in SQL results tab of OBIEE Prompt. 

 

SELECT TIMESTAMPADD (SQL_TSI_DAY,-1,CURRENT_DATE) FROM "Time"

TALEND OPEN STUDIO INTRODUCTION & INSTALLATION

 

Talend is a software integration company.
The company provides Big data, Cloud storage, Data integration, data management, master data management, Data quality, Data preparation and Enterprise Application Integration software and services.

Talend Open Studio is the ETL based data integration tool similar to informatica.

It can be installed in Windows,Linux and Mac.

We need to have JDK and Java (JVM 1.8) installed on our system before Talend Open Studio.

Set the Java PATH and HOME variable in the system variables as shown below.

.

Go to www.Talend.com/download and download the Data Integration Software (Talend Open Studio).

Extract the file and run the file.
Follow the steps as shown in screenshots below.

   Accept and create a new Project.

Accept the Third party packages.

Open Talend Open Studio.

This is the user interface of Talend Open Studio.

 

BIAPPS STANDARD COLUMNS AND DATATYPES

Please find the BIAPPS columns and their standard datatypes in below table.

COLUMN	DATATYPE
_WID	NUMBER(10)
_.AMT/_QTY	NUMBER(28,10)
_ID	VARCHAR2(80 CHAR)
CODE	VARCHAR2(30 CHAR)
_FLAG	CHAR(1)
INTEGRATION_ID	VARCHAR2(80 CHAR) -- Dimension VARCHAR2(400 CHAR) -- Fact
DATASOURCE_NUM_ID	NUMBER(10)
ETL_PROC_WID	NUMBER(10)
X_CUSTOM	VARCHAR2(10 CHAR)
TENANT_ID	VARCHAR2(80 CHAR)

ODI ERRORS AND SOLUTIONS

1.  The error ODI - 1226/1240 occurs if we miss to select sub select option in  blue interface against the yellow interface.

                                                                                                                                                              

2.  The error ODI - 1228 occurs if there is a data issue with the source like if the target table is having lesser precison than the source query or there is a data issue with source like numeric column storing character values etc.
 Note: This is mostly source side issue, so check the source query and data carefully.

   3. The error ODImay error if precision is missing for columns in target table.

                  

STRING CONCATENATION USING XMLAGG AND LISTAGG FUNCTIONS

 STRING CONCATENATION USING XMLAGG AND LISTAGG FUNCTIONS

XMLAGG function will return data in CLOB DATATYPE.

LISTAGG function will return data in VARCHAR2(4000 CHAR).

------------------XMLAGG QUERY---------------------------

SELECT FM_FORM_MST_B.FORMULA_ID    FORMULA_ID,
       RTRIM(XMLAGG(XMLELEMENT(E,COLNAME,',').EXTRACT('//TEXT()') ORDER BY COLNAME).GETCLOBVAL(),',') X_REASON_FOR_REVISION
FROM    APPS.FM_TEXT_TBL   FM_TEXT_TBL, APPS.FM_FORM_MST_B   FM_FORM_MST_B
WHERE   (1=1)
      AND (FM_TEXT_TBL.TEXT_CODE (+)=FM_FORM_MST_B.TEXT_CODE)
      AND (FM_TEXT_TBL.PARAGRAPH_CODE (+) = 'RSRV')
      AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

----------------QUERY TO TRIM STRING 3900 CHARACTERS-----------------------

  SELECT FM_FORM_MST_B.FORMULA_ID FORMULA_ID,
         DBMS_LOB.SUBSTR( RTRIM(XMLAGG(XMLELEMENT(E,FM_TEXT_TBL.TEXT,',').EXTRACT('//TEXT()') ORDER BY FM_TEXT_TBL.LINE_NO).GETCLOBVAL(),',')  , 3900,1) X_REASON_FOR_REVISION
--If you set the limit to 4000 characters the query might fail as sometimes it might extend beyond 4000 characters so setting it to 3900 characters.
    FROM APPS.FM_TEXT_TBL FM_TEXT_TBL, APPS.FM_FORM_MST_B FM_FORM_MST_B
    WHERE     (1 = 1)
         AND (FM_TEXT_TBL.TEXT_CODE(+) = FM_FORM_MST_B.TEXT_CODE)
         AND (FM_TEXT_TBL.PARAGRAPH_CODE(+) = 'RSRV')
         AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

------------------LISTAGG QUERY-----------------------

SELECT 
    FM_FORM_MST_B.FORMULA_ID    FORMULA_ID,
    LISTAGG (FM_TEXT_TBL.TEXT, '') WITHIN GROUP (ORDER BY FM_TEXT_TBL.LINE_NO)    X_REASON_FOR_REVISION
FROM    APPS.FM_TEXT_TBL   FM_TEXT_TBL, APPS.FM_FORM_MST_B   FM_FORM_MST_B
WHERE    (1=1)
 AND (FM_TEXT_TBL.TEXT_CODE (+)=FM_FORM_MST_B.TEXT_CODE)
AND (FM_TEXT_TBL.PARAGRAPH_CODE (+) = 'RSRV')
AND FM_TEXT_TBL.LINE_NO <> -1
GROUP BY FM_FORM_MST_B.FORMULA_ID;

-------------------------------------------------------

EBS QUERY TO GET OPM FORMULA - REASON FOR REVISION

  SELECT fm_form_mst_b.formula_id formula_id,
         LISTAGG (fm_text_tbl.text, '')
             WITHIN GROUP (ORDER BY fm_text_tbl.line_no)
             reason_for_revision
    FROM apps.fm_text_tbl fm_text_tbl, apps.fm_form_mst_b fm_form_mst_b
   WHERE     (1 = 1)
         AND (fm_text_tbl.text_code(+) = fm_form_mst_b.text_code)
         AND (fm_text_tbl.paragraph_code(+) = 'RSRV')
         AND fm_text_tbl.line_no <> -1
GROUP BY fm_form_mst_b.formula_id;

EBS QUERY TO FIND INVENTORY CYCLE COUNT

SELECT To_char(mtl_cycle_count_entries.cycle_count_entry_id) cycle_count_entry_id,
       hr_all_organization_units.name                        organization_name,
       mtl_system_items_b.segment1                           inventory_item,
       CASE
              WHEN mtl_cycle_count_classes.hit_miss_tolerance_negative IS NULL THEN mtl_cycle_count_headers.hit_miss_tolerance_negative
              ELSE mtl_cycle_count_classes.hit_miss_tolerance_negative
       END hit_miss_tolerance_negative,
       CASE
              WHEN mtl_cycle_count_classes.hit_miss_tolerance_positive IS NULL THEN mtl_cycle_count_headers.hit_miss_tolerance_positive
              ELSE mtl_cycle_count_classes.hit_miss_tolerance_positive
       END hit_miss_tolerance_positive
FROM   apps.mtl_cycle_count_entries,
       apps.mtl_cycle_count_classes,
       apps.mtl_cycle_count_headers,
       apps.mtl_cycle_count_items,
       apps.hr_all_organization_units,
       apps.mtl_system_items_b
WHERE  mtl_cycle_count_entries.organization_id = mtl_cycle_count_classes.organization_id
AND    mtl_cycle_count_entries.cycle_count_header_id = mtl_cycle_count_classes.cycle_count_header_id
AND    mtl _cycle_count_entries.cycle_count_header_id = mtl_cycle_count_headers.cycle_count_header_id
AND    mtl_cycle_count_entries.inventory_item_id = mtl_cycle_count_items.inventory_item_id
AND    mtl_cycle_count_entries.cycle_count_header_id = mtl_cycle_count_items.cycle_count_header_id
AND    mtl_cycle_count_classes.abc_class_id = mtl_cycle_count_items.abc_class_id
AND    mtl_cycle_count_entries.organization_id = hr_all_organization_units.organization_id
AND    mtl_cycle_count_entries.inventory_item_id = mtl_system_items_b.inventory_item_id;