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

  String Function  

Properties à Browse for file 7 file = D :/ shilpa / invoice.txt7GO to Columns7Loading a text file with a single record

Column Name                          SQL Type                     Scale

Records                                    Varchar                           255

Now, click on view data

Transformer7Properties7Create 2 new columns

 

  • Type char 1
  • Data varchar 100

 

  • Now drag in records to data

Screenshot_17  

  • 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
HHINVC  23409CID0454321200802000L
T 
T 
H 

 

Add Some More Column Names

In Voice no      Varchar           10
Cust id                Varchar10
Bill date             Varchar10
Currency             Varchar10
Product id           Varchar10
Quantity              Varchar10
Net amount        Varchar10

 

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  7  Transform  1   7    Transform 2   7     Data set  

               ↓

File = D: / Shilpa / emp.txt  

7Read it with Single Column  (that is columns)   (rec integer 255)

7first line is column Name = True

Transformer 1:-   (Removing Comma)

Click on OUT7Click on load column Definitions Symbol7Select 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     7   Transformation  7 -Data set  

     ↓

Load an emp file

     ↓

Transformer7Properties7Drag into out7Create 3 New stage variables7Using 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

  String function-change date

DD 7 Concat 7 dd: 7dd:’/’ 7 Contact 7 DD:’/’ MM:’/’:YY  

  • Task 2 

Create a new  column

COMM – VAL    varchar  8

7change the Hire data type to varchar

7 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 )

7Compile and RUN

Parameters 

EX:-

Int a = 6, b= 5, c                                              

Compile-time

C= a+b

7Here we are Passing values through Compile time Known as “Static Binding”.

Ex:-  int a, b, c;

<in >>a>>b;                    

Run time

C = a+b

7Passing Value at Run time is known as “Dynamic binding”

7Now, we have used Static Binding, Now, we  See Dynamic Binding using Parameterization.

Parameterization

It is a technique for passing values at run time.

7 It is more advantageous

Parameterization  

  • 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 7 Read Mode = Table   Table = EMP7Columns à load 7 Come to properties 7 click on password7Insert job parameter7New   (or)7Click on job properties Symbol7Parameters  

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

  ok7click on password on left side7insert job parameter, Password  = # PWD , User = #UID#, Remote Server  =# CS#

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

10 (Enter) Add

20 (Enter)Add

30 (Enter)Add

7ok7Right click 7job parameter7Select DNO7DNO = in. DEPT NO7 Ok7Ok  

Data set 

  • File àinsert job parameter à New

  DRIVE                   IP                                  String                D:1

FOLDER                FOLDER                      String                  Shilpal

TRG_FILE            TARGET                        String                  Parameter. Ds

  7ok

7# DRIVE ## FOLDER ## TRG _ FILE #

7Compile

  • 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  

NameValue
User idScott
passwordTiger
ServerOracle
dependent30, 20, 10
inputD:1/
folderShilpal/
targetParam . ds

 

  • Reuse:-  (To reuse in Multiple jobs)

Click on  job properties 7 parameter 7 Select UID, PWD, CS7click on create parameter Set 7 parameter Set Name = Oracle – shilpa7ok 7 Save it in Table Definitions 7 ok 7Now, 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 7Repository7Table Definition 7oracle – shilpa 7 Double click 7 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

NameValue
User idScott
passwordTiger
ServerOracle
dependent30, 20, 10
inputD:1/
folderShilpal/
targetParam. 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? 

Transformer Execution Order  

 Calculating Experience

  Calculating experience  

Experienceinteger7
Exp-monthsinteger7
Exp-daysinteger7
Week-joininteger7
Quarter-joininteger7

  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

  7Compile and RUN

Transformation with Sorting 

Create a file  

E idE nameAccount
111SumanSavings
222Kumarloans
111Sumancurrent
333umaloans
111sumancredit
222Kumarsavings
333umainsurance
111sumaninsurance
222Kumarcurrent

  Case 1

Datastage-sorting  

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