Getting view names and definition of view in Netezza

To get list of all the views in Netezza use the below statement:

SELECT viewname FROM _v_view WHERE viewname NOT LIKE '\_%' AND UPPER(objtype) = 'VIEW' ORDER BY viewname;

To get the definition of a view in Netezza  use the below statement. 

SELECT definition FROM _v_view WHERE viewname = <VIEWNAME> AND UPPER(objtype) = 'VIEW';

To get the unique object id associate with the view in Netezza use the below statement:

SELECT objid FROM _v_view WHERE viewname = <VIEWNAME> AND UPPER(objtype) = 'VIEW';


General syntax to create views in Netezza is :

create view vendor_view as select vendor_id, vendor_name from vendor order by vendor_id;

To read about mat views check:

http://dwbitechguru.blogspot.ca/2014/12/what-is-materialized-mat-view-in.html 

Simple data conversion , string handling, testing conditions in Informatica expression transformation

Simple data conversion , string handling, conditions in Informatica expression transformation

Testing conditions in Informatica expression transformation use:

1) If condition in Informatica:

IIF(condition, true, false)

2) To test for null

ISNULL(<portname>)

When used along with IIF statement, it would look like:

IIF(ISNULL(NAME), '', NAME)

NAME is the port that you are testing to check if it is null. If null you are passing '' blank character otherwise you are sending the NAME as it is .

3) To test if the incoming port is a decimal or integer you can use the below functions:

IS_DECIMAL() or IS_INTEGER() or IsNumber()

For example:

IIF( Not IS_INTEGER(i_PRICE) OR ISNULL(i_PRICE),'Invalid Price', '')

4) To check length of a port which is of string data type :

IIF(LENGTH(i_NAME) > 0, i_NAME, '')

String Handling in Informatica expression transformation use:

1) To search for a string in a port and get the position of the string you can use INSTR Function. SUBSTR function can be used to used to
extract part of string>

substr(NAME,1,INSTR(NAME,'-',1)-1)

In the above example, everything upto - is extracted from the incoming port NAME.

2) To concat strings from multiple ports use || funcation. For example:

i_port1 || i_port2

3) REPLACESTR function can be replace characters in a string; For example, char(10), chr(13) are replaced with '' blanks in the port NAME.

IIF(isnull(NAME),' ', REPLACESTR (1, NAME,CHR(10),CHR(13), ''))

In the below example, - are removed from date ID and then converted to integer:

TO_INTEGER(REPLACECHR(FALSE, v_DATE_ID, '-', ''))

4) To remove blanks from a port you can use LTRIM or RTRIM functions: In example below, blank spaces are removed before checking if the value  of port NAME is equal to '?'.

IIF(LTRIM(RTRIM(NAME)) = '?', 'N', 'Y')

Date manipulation in Informatica expression transformation:

In the below example, port TIMESTAMP is converted to dates in YYYY-MM-DD and YYYY-MM-DD HH24:MI:SS format respectively.

TO_CHAR(trunc(TIMESTAMP), 'YYYY-MM-DD')

TO_DATE(TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')

Netezza datatype conversion function

Netezza datatype conversion function

To convert decimal to integer use cast(value as int) in netezza:

select cast(12345.30 as int)

In the above example in place of 12345.30 you can use any decimal or numeric column.