Hi
In this entry I am going to show how to select an unmapped column and insert into our target table.This method mostly useful when you need to developed your custom query
( such as hierarchical or Subquery factoring )
Step1:
I prepared following interface which maps country information.
Target mappings:
As we can see above , i did not map swift_code column and set its parameter to UD1
Step2:
Now i am taking standart IKM-SQL Control Append KM and modify its "insert new rows" section
as follows
insert into <%=odiRef.getTable("L","TARG_NAME","A")%>
(
<%=odiRef.getTargetColList("", "[COL_NAME]", ", ", "", "UD1")%>
)
<%for (int i=odiRef.getDataSetMin(); i <= odiRef.getDataSetMax(); i++){%>
<%=odiRef.getDataSet(i, "Operator")%>
select <%=odiRef.getPop("DISTINCT_ROWS")%>
<%=odiRef.getSrcColList(i,"","[COL_NAME]","--",",\n","")%>
from <%=odiRef.getFrom(i)%>
where <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>
JRN_FLAG <> 'D'
<%} else {%>
(1=1)
<% } %>
<%=odiRef.getJoin(i)%>
<%=odiRef.getFilter(i)%>
<%=odiRef.getJrnFilter(i)%>
<%=odiRef.getGrpBy(i)%>
<%=odiRef.getHaving(i)%>
<%}%>
and after execution my final query
odiRef.getSrcColList (Source : Oracle.com )
Description
This method available in LKMs and IKMs, returns properties for a list of columns in a given dataset. This list includes all the columns of the sources processed by the LKM (from the source) or the IKM (from the staging area). The list is sorted by the column position in the source tables.
Parameters
pDSIndex
Index identifying which of the datasets is taken into account by this command.
pStart
This sequence marks the beginning of the string to generate.
pUnMappedPattern
The pattern is repeated for each occurrence in the list if the column is not mapped.
pMappedPattern
The pattern is repeated for each occurrence in the list, if the column is mapped.
pSeparator
This parameter separates patterns.
pEnd
This sequence marks the end of the string to generate.
19 Ekim 2017 Perşembe
10 Nisan 2017 Pazartesi
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:
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
6 Nisan 2017 Perşembe
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:
29 Ocak 2017 Pazar
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:
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:
Kaydol:
Kayıtlar (Atom)