String Functions in DataStage

String Functions

1. Left
2. Right
3. Substring
4. 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.txtGO to ColumnsLoading a text file with a single record

Column Name                          SQL Type                     Scale

Records                                    Varchar                           255

Now, click on view data

TransformerPropertiesCreate 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 1^st 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 SymbolSelect 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

     ↓

TransformerPropertiesDrag into outCreate 3 New stage variablesUsing 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 1^st 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

Inclined to build a profession as Datastage Developer? Then here is the blog post on, explore Datastage Training

Advantages:-

1. Local parameters can be Reused
2. 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 = EMPColumns à load  Come to properties  click on passwordInsert job parameterNew   (or)Click on job properties SymbolParameters  

Parameter name	prompt	Type	Default value
UID	user id	String	Sc
PWD	password	Encrypted	Tiger
CS	Server	String	Oracle

  okclick on password on left sideinsert job parameter, Password  = # PWD , User = #UID#, Remote Server  =# CS#

Transformer  properties  Drag in to out  click on ConstraintDouble click  Right click  job parameter  New   DNO                               Department               list             

10 (Enter) Add

20 (Enter)Add

30 (Enter)Add

okRight click job parameterSelect DNODNO = in. DEPT NO OkOk  

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, CSclick on create parameter Set  parameter Set Name = Oracle – shilpaok  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 RepositoryTable 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

For an in-depth understanding of DataStage click on

• Introduction to DataStage
• Architecture of Data Stage
• Oracle Enterprise in Data Stage
• SCD(Slow changing Dimension) in Data Stage
• DataStage Tutorials