OBIEE TO_DATETIME function

TO_DateTime is secret function in OBIEE which is rarely known.

It is similar to TO_DATE function in SQL.

This function is not visible in OBIEE Answers, but you can use this.

Syntax :

TO_DATETIME('string1', 'DateTime_formatting_string')

Example:

case when "Test Result Dates/Times"."Test Begin Date"=to_datetime(CAST('1/1/1901' AS CHAR),'MM/DD/YYYY') THEN NULL ELSE "Test Result Dates/Times"."Test Begin Date"  END

HOW TO SET CUSTOM DATE FORMAT IN SQL USING NLS DATE FORMAT

We can execute the below script for displaying custom date format in sql. We can change the date format as per our requirement by changing the format string highlighted in bold.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

IF you want to set it by default while you login, you can write a trigger as below.

CREATE OR REPLACE TRIGGER DATE_TRIG
AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
END;

HOW TO SET UP CUSTOM DATETIME FORMAT IN SQL DEVELOPER

1. GO TO Menu -->Tools --> Preferences
2. In Preferences dialog --> select Database --> NLS from the left panel.
3. In NLS Parameter list --> set DD-MON-YY HH24:MI:SS in Date Format field.                                                                                                                                                                                                                 
4. 

HOW TO DISPLAY SPACE BETWEEN SYMBOL(LIKE $) AND THE METRIC VALUE IN OBIEE

Use  CSS Style: word-spacing: 5em in Column Format Values Properties to 

display space between symbol and Number.

Also set the Data Format to Custom and  Custom Nemeric Format to "$ #.#0" 

For example if we want to display space between $ and Amount we can use this

and this will display value as "$ 5000" .

This can be used to display space betwen any symbols.

EBS QUERY TO FIND PARENT ITEM AND ITS RELATED SKEW ITEMS

The below query will return the skew items(Packaging Item) for a parent item.
Join any of your tables to mtl_system_items_b and it will return the skew items.

SELECT MSIB.SEGMENT1  PARENT_ITEM,

       MSIB1.SEGMENT1 SKEW_ITEM

FROM   MTL_SYSTEM_ITEMS_B MSIB,

       MTL_CATEGORIES_B_KFV B,

       MTL_CATEGORY_SETS_TL C,

       MTL_ITEM_CATEGORIES D,

       MTL_SYSTEM_ITEMS_B MSIB1

WHERE  1 = 1

       AND B.CONCATENATED_SEGMENTS = MSIB.SEGMENT1

       AND C.CATEGORY_SET_NAME LIKE  '%PARENT CODE'

       AND C.LANGUAGE = 'US'

       AND D.CATEGORY_ID = B.CATEGORY_ID

       AND D.CATEGORY_SET_ID = C.CATEGORY_SET_ID

       AND D.ORGANIZATION_ID = MSIB.ORGANIZATION_ID

       AND D.INVENTORY_ITEM_ID <> MSIB.INVENTORY_ITEM_ID

       AND D.INVENTORY_ITEM_ID = MSIB1.INVENTORY_ITEM_ID

       AND D.ORGANIZATION_ID = MSIB1.ORGANIZATION_ID;

HOW TO DISPAY SECONDS IN HH:MI:SS FORMAT IN OBIEE

We can use OBIEE custom format to display  seconds to dd Days hh:mi:ss instead of writing complex logic.

Use the following custom format string  [duration(sec)] dd "Days" hh:mm:ss

1. Go to OBIEE column Properites --> Data Format
2. Select Override Default Data Format Option
3. Set Treat Number as to Custom and set the format to [duration(sec)] dd "Days" hh:mm:ss

Note: String literals should be enclosed in " " .
          For Ex: in our case we have enclosed Days string in " ".

Similarly we can convert Days or Hours or Minutes into required format by using the below custom Format literals.

[duration(hour)] dd "Days" hh:mm:ss
[duration(min)] dd "Days" hh:mm:ss
[duration(day)] dd "Days" hh:mm:ss

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)