PL/SQL - Boyer Moore algorithm

Hi
  In computer science, the Boyer–Moore string search algorithm is an efficient string searching algorithm that is the standard benchmark for practical string search literature.It was developed by Robert S. Boyer and J Strother Moore in 1977.The algorithm preprocesses the string being searched for (the pattern), but not the string being searched in (the text).

The key features of the algorithm are to match on the tail of the pattern rather than the head, and to skip along the text in jumps of multiple characters rather than searching every single character in the text. (Source:Wikipedia )


Following code is an implementation of Boyer-Moore uses PL/SQL

CODE:

TEST:
PATTERN:='abacbb';
TEXT:='abacaxbaccabacbbaabb';-- FOUND

PATTERN:='74879';
TEXT:='1256564114132775648574879577437273127615761516541189456588322';
--FOUND

PATTERN:='cite';
TEXT:='You have got a funny face, man';  --NOT FOUND

PATTERN:='banana';
TEXT:='I wish I had_an apple instead of'; --NOT FOUND

PATTERN:='74879';
TEXT:='125656411413277564857487 9577437273127615761516541189456588322';
-- NOT FOUND

PATTERN:='LEAD';
TEXT:='JIMY_HAILED_THE_LEADER_TO_STOP';--FOUND

CASE/WHEN-changing rows to columns

Hi ,
In this entry i am gonna show  how to change row to colums using case when statement.

At first lets say we have a following table

CREATE TABLE  TEST_1
(
FACTORY_NAME VARCHAR(100 CHAR),
SALES NUMBER(20),
PRODUCTION NUMBER(20)
)

Current View:

What i want :

Step1:
creating a dummy table
      (
      SELECT
      'TOTAL_SALES'  AS  TEXT FROM DUAL
      )
      UNION
      (
      SELECT
      'TOTAL_PRODUCTION'  AS  TEXT FROM DUAL
      )

Step2:
Combining it with my main table  (no join needed )

SELECT
*
FROM
(
      (
      SELECT
      'TOTAL_SALES'  AS  TEXT FROM DUAL
      )
      UNION
      (
      SELECT
      'TOTAL_PRODUCTION'  AS  TEXT FROM DUAL
      )
),
(
   SELECT
   FACTORY_NAME ,
   SALES AS TOTAL_SALES,
   PRODUCTION  AS TOTAL_PRODUCTION
   FROM
   TEST_1
)

Step 3:
Adding Case when structure to prepare main view

SELECT
    TEXT,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_1'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_1'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_1,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_2'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_2'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_2,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_3'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_3'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_3
FROM
(
      (
      SELECT
      'TOTAL_SALES'  AS  TEXT FROM DUAL
      )
      UNION
      (
      SELECT
      'TOTAL_PRODUCTION'  AS  TEXT FROM DUAL
      )
),
(
   SELECT
   FACTORY_NAME ,
   SALES AS TOTAL_SALES,
   PRODUCTION  AS TOTAL_PRODUCTION
   FROM
   TEST_1
)

,






Step 4:
Getting rid of null values. ( can be done with filtering too )

SELECT
TEXT , SUM(FACTORY_1) FACTORY_1 ,SUM(FACTORY_2) FACTORY_2 ,SUM(FACTORY_3) FACTORY_3
FROM
(

SELECT
    TEXT,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_1'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_1'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_1,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_2'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_2'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_2,
    CASE
      WHEN  TEXT='TOTAL_SALES'      AND FACTORY_NAME='FACTORY_3'    THEN TOTAL_SALES
      WHEN  TEXT='TOTAL_PRODUCTION' AND FACTORY_NAME='FACTORY_3'   THEN TOTAL_PRODUCTION
    END  AS FACTORY_3
FROM
(
      (
      SELECT
      'TOTAL_SALES'  AS  TEXT FROM DUAL
      )
      UNION
      (
      SELECT
      'TOTAL_PRODUCTION'  AS  TEXT FROM DUAL
      )
),
(
   SELECT
   FACTORY_NAME ,
   SALES AS TOTAL_SALES,
   PRODUCTION  AS TOTAL_PRODUCTION
   FROM
   TEST_1
)

)
GROUP BY TEXT
ORDER BY TEXT DESC




Final View:

Tableau analytic functions

Hi

In this entry I will explain how to implement analytic functions  over Tableau.


Step 1)
I choose EU_Superstore/Orders as data source

Step2)
Going to sheet1  and adding my dimension ( Country , State, City )

Step3)
Now the values I want to implement

SUM(Sales) Over ( Partition by Country )
SUM(Sales) Over ( Partition by Country,State )
SUM(Sales) Over ( Partition by Country,State,City )

To do that first I am adding my measure  CALC_1  ( Formula  : SUM([Sales])  )
into my measure section.

By using my CALC_1 variable  I am gonna create a new calculation and define its partition

Step4)
Dragging new measure into report  section  ( showing country total )

Step5)
Now using  CALC_1 , I am gonna create two more  variables as same as the first one but their partition types will be different.

For  SUM(Sales) Over ( Partition by Country,State )

For SUM(Sales) Over ( Partition by Country,State,City )

Final result will be look like this:

Import location for ODI 12c knowledge modules

Hi

ODI 12c  KMs  can be imported  from the directory below

Installation_Directory\ODI\odi\sdk\xml-reference

Example:
C:\ORACLE\ODI\odi\sdk\xml-reference

ODI 12c Split & Aggregate

Hi

In this entry , i will show how to use split and aggregate objects of ODI 12c.


* Source Table: data_1

What i am gonna do is  implementing a multi insert mapping  which loads data into different tables.

Target tables:
data_3 ( it only holds data whose group_name  is  'GROUP1'  )
data_other ( it only holds data whose group_name  is not 'GROUP1'  )



Step1: Adding tables and split object

Step2 : Setting up split object properties

Step3: Adding aggregate object  for data_3 table

Final View:

Physical Diagram:

Results:

data_3 table:

data_other table

ODI 12c - creating MySQL master repository

Hi

In this entry i will show how to create a master repository over MySQL db.


Version :  MySQL Server 5.6


At first , logging into Master repository creation wizard and i have following menu

I am gonna create my master repository over  test database so

JDBC URL :    jdbc:mysql://127.0.0.1:3306/test

*If you are using MySQL (release 5.5.14 or higher) to setup a repository, and the database charset is set to utf8, you will need to add the following parameters to the repository's jdbc URL to operate non-ASCII data correctly in Oracle Data Integrator Studio
( Source : http://docs.oracle.com/cd/E28280_01/install.1111/e16453/manual.htm#ODING492  )

JDBC URL :   jdbc:mysql://127.0.0.1:3306/test?useUnicode=yes&characterEncoding=utf8


Registry Database Name : test   ( Db  i am gonna use for master )


User:    Any user you create with admin privileges over MySQL
( root user cant work here bcoz ODI makes it uppercase automatically so it raise a connection error )


DBA User :  You can use root user  in here or the user you create above.




Result  will be look like this :

ODI 12c might raise warning for some of the parameters in MySQL.In order to avoid this ,you can set following parameters in your my.ini file (located under your mysql  installation folder) .

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
innodb_file_format=Barracuda
innodb_large_prefix=1
log_bin_trust_function_creators=1

ODI 12c - Implementing For Loops

Hi

in  previous versions of ODI   i had a chance to create  "for loops"  in packages , so now i am gonna explain how we can implement a for loop in ODI 12c universe.

First i am gonna reverse a table that contains branch information.


Table structure as follows:

What i want to do is  to get all data except for the one with maximum ORDER_NUM ( Normally we can do this by using bulk SQL but this time i am gonna create  a for loop and making  row by row insertion.  )

As usual i need variable objects  to implement a standart for loop.

Variable:MAX_COUNT ( holds total number of rows in our source table )

Variable: ORDER_ID_VAR ( set to 0 )

and now I am gonna create an interface that let me transfer data one by one into my target table.Inside the mapping , filter object help me to associate interface with my variables.

Lastly i will create a package to implement my for loop.As you can see below , at first  i am refreshing my variables to assign initial values then ORDER_ID_VAR_INC object ( set variable ) is used for incrementing ORDER_ID_VAR.

ORDER_ID_CHECK_1 (Evaluate variable)  is used for checking whether ORDER_ID_VAR less than MAX_COUNT or not. If it is true , our interface will be called for execution otherwise beep object will be started to finish execution.

target table will be look like this

and operator log for execution