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: