Naaz Vilantakath: May 2012

Saturday, May 19, 2012

Multiply records / rows retrieved Oracle query

EMP_NO EMP_NAME EMP_DEPTID SALARY
----------- -------------- ---------------- -----------
1 aaa 3 8000
2 bbb 5 12500
3 ccc 6 1200
4 ddd 4 500

with data
as
(select level-1 l from dual connect by level < = 100 )
select emp_no, emp_name
from nz_test1, data
where l < emp_deptid
order by 1, 2;

EMP_NO EMP_NAME EMP_DEPTID SALARY
----------- --------------- ---------------- ----------
1 aaa 3 8000
1 aaa 3 8000
1 aaa 3 8000
2 bbb 5 12500
2 bbb 5 12500
2 bbb 5 12500
2 bbb 5 12500
2 bbb 5 12500
3 ccc 6 1200
3 ccc 6 1200
3 ccc 6 1200
3 ccc 6 1200
3 ccc 6 1200
3 ccc 6 1200
4 ddd 4 500
4 ddd 4 500
4 ddd 4 500
4 ddd 4 500

18 rows selected

Monday, May 14, 2012

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

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>

Thursday, May 10, 2012

Oracle plsql procedure for Wide table Quality Check - QC / Data Quality - DQ - tables with large no. of columns

Oracle PL SQL procedure to automatically check for any Leading / Trailing spaces, all [space] and Invalid characters in all VARCHAR / CHAR fields of a table.

create or replace
PROCEDURE Qc_Table_Data (full_tbl_nm IN VARCHAR2, create_table_flag IN NUMBER default null)

IS
schema_nm VARCHAR2(20);
tbl_nm VARCHAR2(40);
db_link VARCHAR2(20);

-- CURSOR c1 IS SELECT column_name FROM all_tab_columns
-- WHERE table_name = UPPER(tbl_nm) AND owner=UPPER(schema_nm)
-- AND (data_type='VARCHAR2' OR data_type='VARCHAR' OR data_type='CHAR');

TYPE tab_col_table_type IS TABLE OF
user_tab_columns.column_name%Type
INDEX BY BINARY_INTEGER;

TYPE refcur IS REF CURSOR;

c2 refcur;
vchar_fields tab_col_table_type;
all_fields tab_col_table_type;
field_cnt NUMBER;
field_cnt_all NUMBER;
flag NUMBER;
dta VARCHAR2(100);
src_cur refcur;
sql_stmt VARCHAR2(300);
fld_nm VARCHAR2(50);
temp_var VARCHAR2(300);
err_det VARCHAR2(50);
insuf_privs exception;
pragma exception_init(insuf_privs, -1031);
name_used exception;
pragma exception_init(name_used, -00955);
table_not_exist exception;
pragma exception_init(table_not_exist, -00942);

BEGIN
field_cnt:=0;
field_cnt_all:=0;
flag:=0;

schema_nm:= SUBSTR(full_tbl_nm, 1, INSTR(full_tbl_nm, '.')-1);
tbl_nm:= CASE WHEN INSTR(full_tbl_nm, '@') <> 0 THEN
SUBSTR(full_tbl_nm, INSTR(full_tbl_nm, '.')+1, INSTR(full_tbl_nm, '@') - INSTR(full_tbl_nm, '.') -1)
ELSE
SUBSTR(full_tbl_nm, INSTR(full_tbl_nm, '.')+1, LENGTH(full_tbl_nm)-INSTR(full_tbl_nm, '.'))
END;
db_link:= LTRIM(full_tbl_nm, schema_nm||'.'||tbl_nm);

-- DBMS_OUTPUT.PUT_LINE(schema_nm||'.'||tbl_nm);

sql_stmt:='SELECT column_name FROM all_tab_columns'||db_link||'
WHERE table_name = UPPER('''||tbl_nm||''') AND owner=UPPER('''||schema_nm||''')
AND (data_type like ''VARCHAR2%'' OR data_type like ''VARCHAR%'' OR data_type like ''CHAR%'')';

OPEN src_cur FOR sql_stmt;
LOOP
FETCH src_cur INTO fld_nm;
IF src_cur%FOUND THEN
field_cnt:=field_cnt+1;
vchar_fields(field_cnt):= fld_nm;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE src_cur;
sql_stmt:='SELECT column_name FROM all_tab_columns'||db_link||'
WHERE table_name = UPPER('''||tbl_nm||''') AND owner=UPPER('''||schema_nm||''')';

OPEN src_cur FOR sql_stmt;
LOOP
FETCH src_cur INTO fld_nm;
IF src_cur%FOUND THEN
field_cnt_all:=field_cnt_all+1;
all_fields(field_cnt_all):= fld_nm;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE src_cur;
-- DBMS_OUTPUT.PUT_LINE(schema_nm||'.'||tbl_nm);

IF field_cnt_all = 0 THEN
raise_application_error(-20001,'Table does not exist. Ensure you provdide schema name.');

ELSE
IF create_table_flag = 1 THEN
EXECUTE IMMEDIATE 'CREATE TABLE QC_TABLE_RESULT(TBL_NAME VARCHAR2(50), COL_NAME VARCHAR2(40), ERR_DETAILS VARCHAR2(50), SAMPLE_DATA VARCHAR2(100), ERR_QRY VARCHAR2(300), RUN_TIME DATE)';
END IF;
-- DBMS_OUTPUT.PUT_LINE(field_cnt);
DBMS_OUTPUT.PUT_LINE('-------------------------------');
--------- For Invalid Characters
FOR i IN 1..field_cnt
LOOP
OPEN c2 FOR 'SELECT '||vchar_fields(i)||' FROM '||schema_nm||'.'||tbl_nm||' WHERE REGEXP_LIKE('||vchar_fields(i)||', ''[^A-Za-z0-9_ ]'')' ;
FETCH c2 INTO dta;
IF c2%FOUND THEN
flag:=1;
temp_var:= 'SELECT '||vchar_fields(i)||', '||tbl_nm||'.* FROM '||schema_nm||'.'||tbl_nm||db_link||' WHERE REGEXP_LIKE('||vchar_fields(i)||', ''[^A-Za-z0-9_ ]'')';
err_det:='INVALID CHARACTERS';
DBMS_OUTPUT.PUT_LINE('INVALID CHARACTERS in " '||vchar_fields(i)||' "; Sample data : "'||dta||'" ; '||temp_var);
IF create_table_flag >= 1 THEN
EXECUTE IMMEDIATE 'INSERT INTO QC_TABLE_RESULT VALUES(:0,:1,:2,:3,:4,:5)' USING schema_nm||'.'||tbl_nm,vchar_fields(i),err_det,dta,temp_var,SYSDATE;
END IF;
END IF;
CLOSE c2;
END LOOP;
COMMIT;
IF flag=1 THEN
DBMS_OUTPUT.PUT_LINE(CHR(10)||'NOTE: To modify filter, add those allowed special characters inside "[]" of regular expression and run query manually.');
DBMS_OUTPUT.PUT_LINE('Run "set define off" to turn off prompt and for -ve symbol, put at the end only.');
DBMS_OUTPUT.PUT_LINE('-------------------------------');
END IF;

--DBMS_OUTPUT.PUT_LINE('1');
--------- For Leading/Trailing spaces
FOR i IN 1..field_cnt
LOOP
OPEN c2 FOR 'SELECT '||vchar_fields(i)||' FROM '||schema_nm||'.'||tbl_nm||' WHERE TRIM('||vchar_fields(i)||') <> '||vchar_fields(i) ;
FETCH c2 INTO dta;
IF c2%FOUND THEN
flag:=2;
temp_var:='SELECT '||vchar_fields(i)||', '||tbl_nm||'.* FROM '||schema_nm||'.'||tbl_nm||db_link||' WHERE TRIM('||vchar_fields(i)||') <> '||vchar_fields(i);
err_det:='LEADING/TRAILING SPACES';
DBMS_OUTPUT.PUT_LINE('LEADING/TRAILING SPACES in " '||vchar_fields(i)||' "; Sample data : "'||dta||' "; '||temp_var);
IF create_table_flag >= 1 THEN
EXECUTE IMMEDIATE 'INSERT INTO QC_TABLE_RESULT VALUES(:0,:1,:2,:3,:4,:5)' USING schema_nm||'.'||tbl_nm,vchar_fields(i),err_det,dta,temp_var,SYSDATE;
END IF;
END IF;
CLOSE c2;
END LOOP;
COMMIT;
IF flag=2 THEN
DBMS_OUTPUT.PUT_LINE('-------------------------------');
END IF;

--------- For ALL spaces
FOR i IN 1..field_cnt
LOOP
OPEN c2 FOR 'SELECT '||vchar_fields(i)||' FROM '||schema_nm||'.'||tbl_nm||' WHERE TRIM('||vchar_fields(i)||') IS NULL AND '||vchar_fields(i)||' IS NOT NULL';
FETCH c2 INTO dta;
IF c2%FOUND THEN
flag:=3;
temp_var:='SELECT '||vchar_fields(i)||', '||tbl_nm||'.* FROM '||schema_nm||'.'||tbl_nm||db_link||' WHERE TRIM('||vchar_fields(i)||') IS NULL AND '||vchar_fields(i)||' IS NOT NULL';
err_det:='ALL SPACES';
DBMS_OUTPUT.PUT_LINE('ALL SPACES in " '||vchar_fields(i)||' "; Sample data : "'||dta||' "; '||temp_var);
IF create_table_flag >= 1 THEN
EXECUTE IMMEDIATE 'INSERT INTO QC_TABLE_RESULT VALUES(:0,:1,:2,:3,:4,:5)' USING schema_nm||'.'||tbl_nm,vchar_fields(i),err_det,dta,temp_var,SYSDATE;
END IF;
END IF;
CLOSE c2;
END LOOP;
COMMIT;
IF flag=3 THEN
DBMS_OUTPUT.PUT_LINE('-------------------------------');
END IF;

----------- For ALL NULLs
FOR i IN 1..field_cnt_all
LOOP
OPEN c2 FOR 'SELECT 1 FROM '||schema_nm||'.'||tbl_nm||' WHERE '||all_fields(i)||' IS NOT NULL' ;
FETCH c2 INTO dta;
IF c2%NOTFOUND THEN
flag:=4;
dta:= NULL;
temp_var:='SELECT '||all_fields(i)||', '||tbl_nm||'.* FROM '||schema_nm||'.'||tbl_nm||db_link||' WHERE '||all_fields(i)||' IS NOT NULL';
err_det:='ALL NULLS';
DBMS_OUTPUT.PUT_LINE('ALL NULLS in " '||all_fields(i)||' "; '||temp_var);
IF create_table_flag >= 1 THEN
EXECUTE IMMEDIATE 'INSERT INTO QC_TABLE_RESULT VALUES(:0,:1,:2,:3,:4,:5)' USING schema_nm||'.'||tbl_nm,all_fields(i),err_det, dta,temp_var,SYSDATE;
END IF;
END IF;
CLOSE c2;
END LOOP;
COMMIT;
IF flag=4 THEN
DBMS_OUTPUT.PUT_LINE('-------------------------------');
END IF;

----------- For Any NULLs
FOR i IN 1..field_cnt_all
LOOP
OPEN c2 FOR 'SELECT 1 FROM '||schema_nm||'.'||tbl_nm||' WHERE '||all_fields(i)||' IS NULL AND 1 IN (SELECT 1 FROM '||schema_nm||'.'||tbl_nm||' WHERE '||all_fields(i)||' IS NOT NULL)' ;
FETCH c2 INTO dta;
IF c2%FOUND THEN
flag:=4;
dta:= NULL;
temp_var:='SELECT '||all_fields(i)||', '||tbl_nm||'.* FROM '||schema_nm||'.'||tbl_nm||db_link||' WHERE '||all_fields(i)||' IS NULL AND 1 IN (SELECT 1 FROM '||schema_nm||'.'||tbl_nm||' WHERE '||all_fields(i)||' IS NOT NULL)' ;
err_det:='ANY NULL';
DBMS_OUTPUT.PUT_LINE('ANY NULL in " '||all_fields(i)||' "; '||temp_var);
IF create_table_flag >= 1 THEN
EXECUTE IMMEDIATE 'INSERT INTO QC_TABLE_RESULT VALUES(:0,:1,:2,:3,:4,:5)' USING schema_nm||'.'||tbl_nm,all_fields(i),err_det, dta,temp_var,SYSDATE;
END IF;
END IF;
CLOSE c2;
END LOOP;
COMMIT;
IF flag=4 THEN
DBMS_OUTPUT.PUT_LINE('-------------------------------');
ELSIF flag=0 THEN
DBMS_OUTPUT.PUT_LINE('No issues...!!!');
END IF;
END IF;

EXCEPTION
WHEN table_not_exist THEN
raise_application_error(-20001,'Create this table CREATE TABLE QC_TABLE_RESULT(TBL_NAME VARCHAR2(50), COL_NAME VARCHAR2(40), ERR_DETAILS VARCHAR2(50), SAMPLE_DATA VARCHAR2(100), ERR_QRY VARCHAR2(300), RUN_TIME DATE)'|| CHR(13) ||'Then run the procedure with create_table_flag as 2 ');
WHEN NAME_USED THEN
raise_application_error(-20001,'Table QC_TABLE_RESULT already existing in your schema'|| CHR(13) ||' Drop existing table and create this table CREATE TABLE QC_TABLE_RESULT(TBL_NAME VARCHAR2(50), COL_NAME VARCHAR2(40), ERR_DETAILS VARCHAR2(50), SAMPLE_DATA VARCHAR2(100), ERR_QRY VARCHAR2(300), RUN_TIME DATE) and then run the procedure with create_table_flag as 2 ');
WHEN INSUF_PRIVS THEN
raise_application_error(-20001,'Insufficient privilege to create table through procedure.'|| CHR(13) ||' First create table CREATE TABLE QC_TABLE_RESULT(TBL_NAME VARCHAR2(50), COL_NAME VARCHAR2(40), ERR_DETAILS VARCHAR2(50), SAMPLE_DATA VARCHAR2(100), ERR_QRY VARCHAR2(300), RUN_TIME DATE) and then run the procedure with create_table_flag as 2 ');
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Procedure will create QC_TABLE_RESULT.

Results will be automatically archived to table QC_TABLE_RESULT:

SELECT * FROM QC_TABLE_RESULT;

Note: Since results of above procedure are printed using DBMS_OUTPUT.PUT_LINE(), you have to run through Oracle SQL Developer tool --> Navigate to the above procedure "QC_TABLE_DATA" --> Right Click --> Click Run

While running, input table name and below are the options for 2nd parameter

Parameter CREATE_TABLE_FLAG is optional and works as follows.

NULL : Output of the procedure will be printed in Script Output tab or Running tab of SQL Developer

1 : Procedure will create table QC_TABLE_RESULT in your schema and insert findings in to it.

2 : Archiving mode - Assuming table QC_TABLE_RESULT is already created; procedure inserts its findings to it.

Test table
CREATE TABLE "QC_TEST_TABLE" ("EMPID" NUMBER, "NAME" VARCHAR2(50 BYTE), "SALARY" NUMBER, "EMP_TITLE" VARCHAR2(50 BYTE));
INSERT INTO "QC_TEST_TABLE" (EMPID, NAME, EMP_TITLE) VALUES ('1', 'Rajesh', 'Analyst');
INSERT INTO "QC_TEST_TABLE" (EMPID, NAME, EMP_TITLE) VALUES ('2', 'Lohit', 'Manager');
INSERT INTO "QC_TEST_TABLE" (EMPID, NAME, EMP_TITLE) VALUES ('3', 'Manish', 'Supervisor');
INSERT INTO "QC_TEST_TABLE" (EMPID, NAME, EMP_TITLE) VALUES ('4', 'Rah$l', 'Admin');
INSERT INTO "QC_TEST_TABLE" (EMPID, NAME, EMP_TITLE) VALUES ('5', ' Pavan', 'Accountant');
INSERT INTO "QC_TEST_TABLE" (EMPID, NAME, EMP_TITLE) VALUES ('6', 'Prasad', ' ');
INSERT INTO "QC_TEST_TABLE" (EMPID, NAME) VALUES ('7', 'Prasad');
COMMIT;

Wednesday, May 9, 2012

Cognos Report Studio Expressions/ Formulas

Applying Filter on date field to retrieve only past 12 months data

Extract(year,[Date])=Extract(year,{sysdate})
OR Extract(year,[Date])=Extract(year,{sysdate}) -1
AND Extract(month,[Date])
BETWEEN Extract(month,{sysdate})+1 AND 12

Applying Filter on date field to retrieve only last week's data

[Query1].[Date] > _add_days (date2timestamp (Today ()), -7 )