Netezza Functions

SQL server equivalent functions in Netezza

** As Netezza is case sensitive be careful with String comparison (best practice to use upper/lower case when comparing string columns both the sides) this applicable for joining group by as well.

The exception is when one wants to find a specific string (ex: - I want to search only ‘NeteZZA’ in my column).

String Functions
Date Functionss 

Sno	SQL Server Functions	Netezza Functions	Purpose of the function
	SUBSTRING(CASE_NO, PATINDEX('%[^0 ]%', CASE_NO+ ' '), LEN(CASE_NO))	ltrim(CASENUMBER,'0')	Remove left hand side zero’s from case number
		RTRIM (CASENUMBER,'0') EX:- RTRIM (0011124500,'0') Result:- 00111245	Remove right hand side zero’s from case number
	LEFT()	substr() EX:-  substr(‘VAMSHI’,0,3) Result ‘VA’	When we want only LEFT had side last 3 digits or charades
	RIGHT()	substr('VAMSHI',LENGTH('VAMSHI')-1) RESULT:- HI Formula for last 2 alphabets do Length -1 For 3 its length -2 For 4 its Length -3	Use when we need Right hand side digits or charades
	CONVERT(VARCHAR(10), DATE_FLD, 101)	TO_CHAR(RECIEVEDATE,'DD/MM/YYYY')	To convert date to the format we want Ex:- 'DD/MM/YYYY' or ‘DD/MON/YYYY etc.,
	Like Function	Like	The LIKE  function syntax and functionality is same to SQL server
	Case Function	Case	The CASE function syntax and functionality is same to SQL server
DATE FUNCTION (For numeric result)
	Year(RCV_DAY_DAY_DT)	extract(year from RCV_DAY_DAY_DT)	This give us the year EX:- 2014
	MOTH(RCV_DAY_DAY_DT)	extract(MOTH from RCV_DAY_DAY_DT)	This give us the year EX:- 1(JAN)
	DAY(RecieveDate)	extract(DAY from current_date)	This give us day of the year EX:- 14(day of the year)
		extract(WEEK from current_date)	Week of the year EX:- its 38 week of this year 2014
DATE FUNCTION (For String result)
		to_char(DATE_D,'DAY')	EX:- ‘WEDNESDAY’
		to_char(DATE_D,'Month')	EX:- September
Adding months days and to get QUARTER out of DATE
		SELECT EXTRACT(QUARTER FROM now());	To get the quarter from DATE
		months_between(d1, d2)	To get the months between 2 dates
		age(t,t)	To get the difference between to dates
		last_day(now())	To get the last day of the month
		add_months(now(),-1))	To Add or subtract a month to the date
		date_part('Month', to_date('2014201', 'YYYYDDD'));	Extract month from julian date in netezza

First 10 records or Top 100 records
	TOP 1	LIMIT 1	It limits the no of records we want to see
		LIMIT 2 OFFSET 2 Ex:- SELECT * FROM TABLE ORDER BY CASENUMBER LIMIT 2 OFFSET 2	Offset will skip the first 2 records and limit will give 3 and 4 records **Order by is key for these functions

How to get part of a string separated any symbol's character

Ex:- Comma, Tab, Star etc.,

Function:-

select get_value_varchar(array_split('IBM_NETEZZA_DB_FUNCTIONS','_') , 4);

 Explanation:-

Actual String                  'IBM_NETEZZA_DB_FUNCTIONS'

Position of the string           1      2    3   4  

Result:- 

           'FUNCTIONS'

Functions available in Netezza(in your database)

Select * from _v_Function

the above query will list all the functions available in database.

Netezza is case sensitive:-

Is there a way to make Netezza case insensitive?

Let's see how it will effect and how to handle it in Netezza.

Example 1:- 

WITH test (NAME)
     AS (SELECT 'Netezza' AS NAME
         UNION
         SELECT 'Netezza Db' AS NAME1)
SELECT NAME
FROM   test
WHERE  NAME = 'netezza' 

Result:-  No records found 

The above behavior is not at all weird, Some one worked on SQL server may feel it as weird (as it is case insensitive)

Example 2:-

WITH test (NAME)
     AS (SELECT 'Netezza' AS NAME
         UNION
         SELECT 'Netezza Db' AS NAME1)
SELECT NAME
FROM   test
WHERE  NAME LIKE '%ntezza%'  

Result:- No records found

Example 3:-

WITH test (NAME)
     AS (SELECT 'Netezza' AS NAME
         UNION
         SELECT 'NeTezZa' AS NAME1
         UNION
         SELECT 'NETEZZA' AS NAME1
         UNION
         SELECT 'netezza' AS NAME1)

SELECT NAME ,COUNT(*) AS COUNT
FROM   test
Group by NAME

Result:-  Name           COUNT    
         "Netezza"          1
         "NeTezZa"          1
         "NETEZZA"          1
         "Netezza"          1
 Case sensitive has to handle in below cases.
1) While joining two string columns
2) Group by and Order by (by string columns)
3) WHERE or LIKE conditions.
4) CASE statements or DECODE statements.


The best way to handle is to handle it in the first place ETL.

To handle the same in Netezza data base we have the below function.

Let's see the difference using the function "UPPER".

WITH test (NAME)
     AS (SELECT 'Netezza' AS NAME
         UNION
         SELECT 'NeTezZa' AS NAME1
         UNION
         SELECT 'NETEZZA' AS NAME1
         UNION
         SELECT 'netezza' AS NAME1)

SELECT UPPER(NAME) ,COUNT(*) AS COUNT
FROM   test
Group by UPPER(NAME)

Result:-  Name           COUNT    
         "NETEZZA"          4