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.