- Left
- Right
- Substring
- Field
EX:-
- Mind Quest
L (7) = Mind Que
R(3) = Que (on Sub String (5) 3)
R (L (7), 3)
- Mathe ndticks
R (L (7), 2) = ma (or) L(R ()0,2) = ma
- Create an text file
HINVE 23409 CID 454321200802DOL
TPID1234 5 12
TPID2345 6 13
TPID3456 7 14

Properties à Browse for file file = D :/ shilpa / invoice.txt
GO to Columns
Loading a text file with a single record
Column Name SQL Type Scale
Records Varchar 255
Now, click on view data
TransformerProperties
Create 2 new columns
- Type char 1
- Data varchar 100
- Now drag in records to data

- Now create a stage variable
type — 0 — varchar — “1”
- Stage Variable left (in. records, i) type
- Compile and RUN
- Display all the 1st Characters:-
Type | Data |
H | HINVC 23409CID0454321200802000L |
T | |
T | |
H |
Add Some More Column Names
In Voice no | Varchar | 10 |
Cust id | Varchar | 10 |
Bill date | Varchar | 10 |
Currency | Varchar | 10 |
Product id | Varchar | 10 |
Quantity | Varchar | 10 |
Net amount | Varchar | 10 |
Add new stage Variables
Right (left (in . records, 10), 9) = in v no
Right (left (in . records, 19), 9) = cid
Left(Right (in . records, 11), 8) = bd
Right (in . records, 3) = curr
Right (left (in . records, 8), 7) = p id
Right (left (in . records, 17), 1) = Quant
Right (left (in . records, 26), 2) = Price
Click on Constraint
Constraint : type = “H”
Output
Type data invoice num Custid bill date currency
H HWCNC INVC CID 454321 20080203 DDl
23409 CID 23409
H
H
H
Reading Comma Separated values of variable length
- Create an emp table with fields
E ID, E NAME, STATE
10, abc, ap
20, def, ap
30, ghi, ap
Create an EMP table with Spaces before, after in between, and some Special Characters
EID, E NAME, STATE
10, @abc, ap
20, def @, ap
30, ghi@, ap
Functions
- TRIMB – Removes after Space
- TRIMF – Removes after Space
- STRIP WHITE SPACES – Removes before, After, in-between spaces
- COMPACTWHITE SPACES
- Remove single Spaces
- IF Multiple Spaces are there, Removes in-between spaces and leaves space
EX:- MIND Q UEST MIND Q UEST
- TRIM – Removes Special Characters
To Eliminate Comma, Spaces, Special character using Functions:-
In — out — ds link 3
S.F Transform 1
Transform 2
Data set
↓
File = D: / Shilpa / emp.txt
Read it with Single Column (that is columns) (rec integer 255)
first line is column Name = True
Transformer 1:- (Removing Comma)
Click on OUTClick on load column Definitions Symbol
Select EMP.txt
Field (in. rec,’,’, 1) = E id
Field (in. rec,’,’, 2) = E Name
Field (in. rec,’,’, 3) = State
[Syntax :- field (% String %, %delimiter%, % user name %)]àCompile and RUN
E id E NAME State
10 abc ap
20 def ap
30 ghi ap
Transformation 2:- (Eliminating Spaces, Special charities )
- Properties
- Up case (Trim (Strip white spaces (Dut E name),”@”)) = E name
- Compile and RUN
E id E NAME State
10 abc ap
20 def ap
ghi ap
Changing the format of date using Type conversion and Date and Time function
- Task 1
We have the Date as 2008-01-03 (YYYY-MM-DD) 00:00:00 (Times stamp)Using field and type conversion functions change it to DD/MM/YY
In out
Oracle enterprise Transformation
-Data set
↓
Load an emp file
↓
TransformerProperties
Drag into out
Create 3 New stage variables
Using field functions
Field (Times stamp To Date (in. Hire data),’_’,3) –DD
Field (Times stamp To Date (in. Hire data),’_’,2) –MM
Field (Times stamp To Date (in. Hire data),’_’,1) –YY
(or)
- Using Type and Date conversion functions
Month Day from Date (Times stamp To Date (in. Hire data)) –DD
Month from Date (Times stamp To Date (in. Hire data)) –MM
Month Year from Date (Times stamp To Date (in. Hire data)) –YY
↓
Now in hiredate

DD Concat
dd:
dd:’/’
Contact
DD:’/’ MM:’/’:YY
- Task 2
Create a new column
COMM – VAL varchar 8
change the Hire data type to varchar
Create another Stage variable Comm is varchar 10
It is null (in. comm) then “Null” else left (in. comm., 2)
(that is, if Comm in NULL displays Null, else display 1st 2 characters )
Compile and RUN
Parameters
EX:-
Int a = 6, b= 5, c
Compile-time
C= a+b
Here we are Passing values through Compile time Known as “Static Binding”.
Ex:- int a, b, c;
<in >>a>>b;
Run time
C = a+b
Passing Value at Run time is known as “Dynamic binding”
Now, we have used Static Binding, Now, we See Dynamic Binding using Parameterization.
Parameterization
It is a technique for passing values at run time.
It is more advantageous

- Local parameters are job Specific p to 7.5 * 2
- Global parameters are used in the entire project
- Reusability in the project through a technique called Parameterization in 8.0.1
Advantages:-
- Local parameters can be Reused
- We can not only pass/assign/value to parameter but can pass multiple values.
Using Parameters
Oracle Enterprise Transformer Data set Properties Read Mode = Table Table = EMP
Columns à load
Come to properties
click on password
Insert job parameter
New (or)
Click on job properties Symbol
Parameters
Parameter name | prompt | Type | Default value |
UID | user id | String | Sc |
PWD | password | Encrypted | Tiger |
CS | Server | String | Oracle |
okclick on password on left side
insert job parameter, Password = # PWD , User = #UID#, Remote Server =# CS#
Transformer
properties
Drag in to out
click on Constraint
Double click
Right click
job parameter
New DNO Department list
10 (Enter) Add
20 (Enter)Add
30 (Enter)Add
ok
Right click
job parameter
Select DNO
DNO = in. DEPT NO
Ok
Ok
Data set
- File àinsert job parameter à New
DRIVE IP String D:1
FOLDER FOLDER String Shilpal
TRG_FILE TARGET String Parameter. Ds
ok
# DRIVE ## FOLDER ## TRG _ FILE #
Compile
- RUN
- Dering RUN time it asks for Dept no
User id = Scott
Dept no = 20
- Display data with Dept no = 20
Out put :– During RUN
Name | Value |
User id | Scott |
password | Tiger |
Server | Oracle |
dependent | 30, 20, 10 |
input | D:1/ |
folder | Shilpal/ |
target | Param . ds |
- Reuse:- (To reuse in Multiple jobs)
Click on job properties parameter
Select UID, PWD, CS
click on create parameter Set
parameter Set Name = Oracle – shilpa
ok
Save it in Table Definitions
ok
Now, whenever we create any other job, we can directly call oracle – shilpa parameter set,
- Oracle
- Properties
- Password = Set to Default
User = Set to Default
Remote Server = Set to Default
password = # oracle _shilpa. PWD #
User = # oracle – shilpa . UID #
Remote Server = # oracle – shilpa.cs #
- Editing:-
View Repository
Table Definition
oracle – shilpa
Double click
Values
Value file name UID PWD CS
Development SCOTT Tiger oracle
Testing abcd abcd renu
Production shilpa shilpa Star
- Ok
- Compile and RUN
Output
Name Value
Oracle – shilpa parameters (AS predefined ) testing production development
Name | Value |
User id | Scott |
password | Tiger |
Server | Oracle |
dependent | 30, 20, 10 |
input | D:1/ |
folder | Shilpal/ |
target | Param. ds |
Transformation Execution order
- Stage variables are Evaluated before the data is moved to the link (that is Transformer)
- The derivation is Evaluated just before the data is moved to the target
- In stage Variable, initialization is possible
- Stage Variables are more advantages
What is the Transformer Execution order?

Calculating Experience

Experience | integer | 7 |
Exp-months | integer | 7 |
Exp-days | integer | 7 |
Week-join | integer | 7 |
Quarter-join | integer | 7 |
The task is to find out the experience in years, months, days, week of joining, Quarter Of joining
Create 2 stage variable
Exp integer 7
Month integer 7
Exp = year from Date (current Date ()) – Year from Date (Times stamp to date (in. Hire date))
Month = Month from Date (Times stamp to date (in. Hire date))
- Now – – > exp = experience
- Exp * 12 = exp – month
- Days Since from Date (current Date) Times stamp to date (in. Hire date) = Exp- days
- Year week from Date (Times stamp to date (in. Hire date)) = week – join
- If month < =3 then1 else if Month >=4 and Month <=6
Then 2 else if month > = 7 and Month <=8 then 3 else 4
Compile and RUN
Transformation with Sorting
Create a file
E id | E name | Account |
111 | Suman | Savings |
222 | Kumar | loans |
111 | Suman | current |
333 | uma | loans |
111 | suman | credit |
222 | Kumar | savings |
333 | uma | insurance |
111 | suman | insurance |
222 | Kumar | current |
Case 1

Transformer
Stage Variables
Previous key 0 Integer/ Tiny int 1
New key “N” char 1
Accents “Y” Varchar 255
Map the key change to prev key
In. key change –prev key
If prev key = 1 then “y” then in. account –accents
Else accounts:”,”: in. account
- Create a new Column:- 1 ACNT – ALL
- Drag accents to ACNT – ALL
- Accents – ACNT – ALL
- Now, Compile and RUN
Output
E ID E NAME Account key a
Case 2
Sequential file —–Sort—-Transformer——Sort——–Data set
Create a new Stage variable
If new accent =”y” then 1 else cnt+1 –– – >cnt
- Sort
Key = Count / Eid
Sort order = Descending
Output
Data set
PartitioningàPartition type = HashàClick on cidàPerform sort, Stable, unique
AlNum(mylink.mystring1) | Checks whether the given string contains only alphanumeric characters. If mylink.mystring1 contains the string “OED_75_9*E”, then the following function would return the value 0 (false) |
Alpha(mylink.mystring1) | Checks whether the given string contains only alphabetic characters If mylink.mystring1 contains the string “12 red roses”, then the following function would return the value 0 (false). |
CompactWhiteSpace(mylink.mystring) | Return the string after reducing all consecutive white space to a single space. If mylink.mystring contains the string “too many spaces”, then the following function returns the string “too many spaces”: |
Compare(mylink.mystring1,mylink.mystring2,L) | Compare two strings |
CompareNoCase(mylink.mystring1,mylink.mystring2) | Compares two strings for sorting, ignoring their case |
CompareNum(mylink.mystring1,mylink.mystring2,4) | If mylink.mystring1 contains the string “Chocolate” and mylink.mystring2 contains the string “Choccy Treat”, then the following function returns the result 0 |
CompareNumNoCase(mylink.mystring1,mylink.mystring2,4) | If mylink.mystring1 contains the string “chocolate” and mylink.mystring2 contains the string “Choccy Treat”, then the following function returns the result 0 |
Convert(“TI”,”XY”,mylink.mystring1) | If mylink.mystring1 contains the string “NOW IS THE TIME”, then the following function returns the string “NOW YS XHE XYME”. |
Count(mylink.mystring1,”choc”) | Counts the number of times a substring occurs in a string If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars”, then the following function returns 3 |
Dcount(mylink.mystring1,”,”) | Counts the number of delimited fields in a string If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars”, then the following function returns 3 |
DownCase(mylink.mystring1) | Changes all uppercase letters in a string to lowercase If mylink.mystring1 contains the string “CaMel cAsE”, then the following function returns the string “camel case”. |
UpCase(mylink.mystring1) | If mylink.mystring1 contains the string “CaMel cAsE”, then the following function returns the string “CAMEL CASE”. |
DQuote(mylink.mystring1) | Encloses a string in double quotation marks If mylink.mystring1 contains the string needs quotes, then the following function returns the string “needs quotes”. |
SQuote(mylink.mystring1) | Encloses a string in single quotation marks. If mylink.mystring1 contains the string needs quotes, then the following function returns the string ‘needs quotes’. |
Field(mylink.mystring1,”,”,2) | If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the string ” chocolate ice cream”. Field(mylink.mystring1,”,”,2)If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the string ” chocolate ice cream, chocolate bars”. Field(mylink.mystring1,”,”,2,2) |
Index(mylink.mystring1,”chocolate”,2) | If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the value 18 |
Left(mylink.mystring1,9) | If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the string “chocolate”. |
Right(mylink.mystring1,7) | If mylink.mystring1 contains the string “chocolate drops, chocolate ice cream, chocolate bars, chocolate dippers”, then the following function returns the string “dippers” |
Num(mylink.mystring1) | Returns 1 if string can be converted to a number, or 0 otherwise If mylink.mystring1 contains the string “22”, then the following function returns the value 1 If mylink.mystring1 contains the string “twenty two”, then the following function returns the value 0 |
PadString(mylink.mystring1,”0″,5) | Return the string padded with the specified number of pad characters IIf mylink.mystring1 contains the string “AB175”, then the following function returns the string “AB17500000”. |
Space(mylink.mylength) | Returns a string of n space characters. If mylink.mylength contains the number 100, then the following function returns a string that contains 100 space characters |
Str(mylink.mystring1,5) | Repeats a string the specified number of times If mylink.mystring1 contains the string needs “choc”, then the following function returns the string “chocchocchocchocchoc”. |
StripWhiteSpace(mylink.mystring) | Returns the string after removing all whitespace characters from it If mylink.mystring contains the string “too many spaces”, then the following function returns the string “toomanyspaces”: |
Trim(mylink.mystring) | If mylink.mystring contains the string ” String with whitespace “, then the following function returns the string “String with whitespace”: |
Trim(mylink.mystring,”.”) | If mylink.mystring contains the string “..Remove..redundant..dots….”, then the following function returns the string “Remove.redundant.dots”: |
Trim(mylink.mystring,”.”,”A”) | |
TrimB(mylink.mystring) | Removes all trailing spaces and tabs from a string. If mylink.mystring contains the string “too many trailing spaces “, then the following function returns the string “too many trailing spaces”: |
TrimF(mylink.mystring) | Removes all leading spaces and tabs from a string If mylink.mystring contains the string ” too many leading spaces”, then the following function returns the string “too many leading spaces”: |
Removes all leading and trailing spaces and tabs from a string If mylink.mystring contains the string ” too many spaces “, then the following function returns the string “too many spaces”: |