Naaz Vilantakath: Informartica Re usable expression transformation for implementing operation as of NVL in Oracle

Save below metadata in a notepad file and rename file extension to xml from txt. Then import xml file through PowerCenter Designer.

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE POWERMART SYSTEM "powrmart.dtd">
<POWERMART CREATION_DATE="05/10/2012 06:52:00" REPOSITORY_VERSION="181.90">
<REPOSITORY NAME="PowerCenterDev02" VERSION="181" CODEPAGE="Latin1" DATABASETYPE="Oracle">
<FOLDER NAME="DATA_QUALITY" GROUP="" OWNER="nsmith6" SHARED="NOTSHARED" DESCRIPTION="" PERMISSIONS="rwx---r--" UUID="e6cb008b-b2cf-4b8c-bde3-b1f4d118473a">
<TRANSFORMATION DESCRIPTION ="" NAME ="EXP_NVL" OBJECTVERSION ="1" REUSABLE ="YES" TYPE ="Expression" VERSIONNUMBER ="1">
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR1" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR1), 'AA',VR1)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out1" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR2" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR2), 'AA',VR2)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out2" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR3" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR3), 'AA',VR3)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out3" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR4" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR4), 'AA',VR4)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out4" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR5" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR5), 'AA',VR5)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out5" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR6" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR6), 'AA',VR6)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out6" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR7" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR7), 'AA',VR7)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out7" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR8" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR8), 'AA',VR8)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out8" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR9" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR9), 'AA',VR9)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out9" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="VR10" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="string" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(VR10), 'AA',VR10)" EXPRESSIONTYPE ="GENERAL" NAME ="VR_out10" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="1000" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM1" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM1), 0, NM1)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out1" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM2" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM2), 0, NM2)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out2" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM3" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM3), 0, NM3)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out3" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM4" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM4), 0, NM4)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out4" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM5" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM5), 0, NM5)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out5" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM6" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM6), 0, NM6)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out6" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM7" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM7), 0, NM7)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out7" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM8" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM8), 0, NM8)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out8" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM9" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM9), 0, NM9)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out9" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="NM10" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="double" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(NM10), 0, NM10)" EXPRESSIONTYPE ="GENERAL" NAME ="NM_out10" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="0"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM1" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM1), 0.00, DCM1)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out1" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM2" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM2), 0.00, DCM2)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out2" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM3" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM3), 0.00, DCM3)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out3" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM4" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM4), 0.00, DCM4)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out4" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM5" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM5), 0.00, DCM5)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out5" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM6" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM6), 0.00, DCM6)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out6" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM7" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM7), 0.00, DCM7)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out7" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM8" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM8), 0.00, DCM8)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out8" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM9" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM9), 0.00, DCM9)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out9" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DCM10" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="decimal" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DCM10), 0.00, DCM10)" EXPRESSIONTYPE ="GENERAL" NAME ="DCM_out10" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="15" SCALE ="6"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DT1" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DT1), ADD_TO_DATE(SYSDATE,'DD',1), DT1)" EXPRESSIONTYPE ="GENERAL" NAME ="DT_out1" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DT2" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DT2), ADD_TO_DATE(SYSDATE,'DD',1), DT2)" EXPRESSIONTYPE ="GENERAL" NAME ="DT_out2" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DT3" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION =" IIF(ISNULL(DT3), ADD_TO_DATE(SYSDATE,'DD',1), DT3)" EXPRESSIONTYPE ="GENERAL" NAME ="DT_out3" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DT4" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DT4), ADD_TO_DATE(SYSDATE,'DD',1), DT4)" EXPRESSIONTYPE ="GENERAL" NAME ="DT_out4" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="" DESCRIPTION ="" NAME ="DT5" PICTURETEXT ="" PORTTYPE ="INPUT" PRECISION ="29" SCALE ="9"/>
<TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="ERROR('transformation error')" DESCRIPTION ="" EXPRESSION ="IIF(ISNULL(DT5), ADD_TO_DATE(SYSDATE,'DD',1), DT5)" EXPRESSIONTYPE ="GENERAL" NAME ="DT_out5" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="29" SCALE ="9"/>
<TABLEATTRIBUTE NAME ="Tracing Level" VALUE ="Normal"/>
</TRANSFORMATION>
</FOLDER>
</REPOSITORY>
</POWERMART>

Naaz Vilantakath

Monday, May 14, 2012

Informartica Re usable expression transformation for implementing operation as of NVL in Oracle

No comments:

Post a Comment

About Me