Netezza / PureData – List of Views against a table

PureData Powered by Netezza

PureData Powered by Netezza

I have found myself using this simple, but useful SQL time in recent weeks to research different issues and to help with impact analysis.  So, I thought I would post it while I’m thinking about it.  It just gives a list of views using a table, which can be handy to know.  This SQL is simple and could be converted to an equi-join.  I used the like statement mostly because I sometimes want to know if there are other views a similar nature in the same family (by naming convention) of tables.

Select All Fields From The _V_View

This is the simplest form of this SQL to views, which a table.

Select * from _v_view

where DEFINITION like ‘%<<TABLE_NAME>>%’ ;

Select Minimal Fields From The _V_View

This is the version of the SQL, which I normally use, to list the views, which use a table.

Select VIEWNAME, OWNER from _v_view

where DEFINITION like ‘%<<TABLE_NAME>>%’;

Related References

Netezza / PureData – How to Substring on a Character

PureData Powered by Netezza

PureData Powered by Netezza

 

I had a reason this week to perform a substring on a character in Netezza this week, something I have not had a need to do before.  The process was not as straightforward as I would have thought, since the command is explained as a static position command, and the IBM documentation, honestly, wasn’t much help.  Knowing full well, that text strings are variable having to provide a static position is not terribly useful in and of itself.  So, we need to use an expression to make the substring command flexible and dynamic.

I did get it work the way I needed, but it took two commands to make it happen:

  • The First was the ’instr’ command to identify the field and character I wanted to substring on: instr(<<FIELD_NAME>>,’~’) as This provides the position number of the tilde (~).
  • The second was the ‘substr’ command in which I embedded the ‘instr’ command: substr(<<FIELD_NAME>>,0,instr(<<FIELD_NAME>>,’~’) )

This worked nicely for what I needed, which was to pick out a file name from the beginning of a string, which was delimited with a tilde (~)

Substring on a Character Command Format

  • This format example starts with position zero (0) as position 1 of substring command and goes to the first tilde (~) as position 2 of the substring command.
Select  <<FIELD_NAME>>

, instr(<>,’~’) as pos2

, substr(<<FIELD_NAME>>,0,instr(<<FIELD_NAME>>,’~’) ) as Results

From <<Table_Name>>

where  <<Where_Clause>>;

 

 

Related references

IBM Knowledge Center, Home, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, Home PureData System for Analytics 7.0.3, IBM Netezza Database User’s Guide, Netezza SQL basics, Netezza SQL extensions, Character functions

Netezza / PureData – How to calculate months between two dates

Netezza, PureData, Months Between Two Dates, function, SQL, difference between two dates

Netezza PureData Months Between Two Dates

Recently, I had reason to during the months between two dates to test some data against business rule/requirement.  Pleasantly surprised I was to find that Netezza had an easy to use function ‘months_between’ function to calculate the difference.

 

The Months Between SQL Function syntax

The months_Between function uses two dates to perform the calculation.  Whether you want the output to be a positive or negative number determines the field order within the function.

  • For a positive number result, put the Newest Date Field first, separated by a comma, then Oldest Date Field
  • For a negative number result, just reverse the order putting the Oldest Date Field first, separated by a comma, then the Newest Date Field

The results will contain a decimal for the days of the month and you will need to round, based on your business requirements, to achieve a whole number.

 

SELECT months_between(<<DateField>>, <<DateField>>) as <<OutputFiledName>>,

from <<TableName>>;

 

Example Months Between SQL

SELECT months_between(current_date, Date(‘2017-01-01’)) as Difference_In_Months,

round(months_between(current_date, Date(‘2017-01-01’)) ) as Difference_In_Months_Rounded

from _v_dual;

 

Related References

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

 

 

 

 

 

Netezza / PureData – How to convert a timestamp to date in SQL

Netezza Convert Timestamp to Date

Netezza Convert Timestamp to Date

 

Since timestamps are integer, which are numbers, the date function can easily convert a timestamp to a date in Netezza SQL.

Date Function Arguments, per IBM Documentation

First argument data type Second argument data type Returns
date numeric(8,0) date

Example Timestamp to Date Conversion SQL

In this example CREATEDATE is stored as a timestamp, despite the field name.

SELECT

DATE(CREATEDDATE)

, COUNT(*) as CNT

FROM Blog.CASES_FS

GROUP BY

date(CREATEDDATE)

ORDER BY

date(CREATEDDATE);

Related References

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.0, IBM Netezza User-Defined Functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations