Tag Archives: aginity workbench for netezza documentation

How To Quote a Single Quote in Netezza SQL?

The short answer is to use four single quotes (””), which will result in a single quote within the select statement results.

How to Assemble the SQL to Quote a Single Quote in a SQL Select Statement

Knowing how to construct a list to embed in a SQL where clause ‘in’ list or to add to an ETL job can be a serious time saver eliminating the need to manually edit large lists. In the example below, I used the Select result set to create a rather long list of values, which needed to be included in an ELT where clause. By:

Adding the comma delimiter (‘,’) and a Concatenate (||) on the front

Followed by adding a quoted single Quote (entered as four single quotes (””)) and a Concatenate (||)

The Field I which to have delaminated and Quoted (S1.ORDER_NUM)

And closed with a quoted single Quote (entered as four single quotes (””))

This results in a delimited and quoted list ( ,’116490856′) which needs only to have the parentheses added and the first comma removed, which is much less work than manually editing the 200 item that resulted from this select.

How to Quick Drop Multiple Tables

occasionally, there is a need to quickly drop a list of tables and you don’t always want to write or generate each command individually in Aginity. So, here is a quick example of how you can use a ‘Select’ SQL statement to generate a list of drop commands for you. Now, this approach assumes there is a common naming convention, so, you may need to adapt it to your needs.

An outline of the Drop Multiple Tables Process

Here is a quick summary of the steps to generate the drop statements from _V_Table:

Recently, while working with a couple of my teammates on different projects I picked up a couple shortcut keys for Aginity for netezza, which I did not know existed. So, I thought about be nice to put a list of shortcut keys for future reference. I don’t use most of them very often, but I have flagged the ones that I have found to be frequently useful. I hope you find this useful as well.

If you want to describe a PureData / Netezza table in SQL, it can be done, but Netezza doesn’t have a describe command. Here is a quick SQL, which will give the basic structure of a table or a view. Honestly, if you have Aginity Generating the DDL is fast and more informative, at least to me. If you have permissions to access NZSQL you can also use the slash commands (e.g. \d).

The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string. However, it is possible, with a little creativity, to substring based on the position of a character in the string. This approach give more flexibility to the substring function and makes the substring more useful in many cases. This approach works fine with either the substring or substr functions. In this example, I used the position example provide the numbers for the string command.

Example Substring SQL

Netezza PureData Substring Function On Specific Character In String

Substring SQL Used In Example

select LOCATIONTEXT

,position(‘,’ in LOCATIONTEXT) as Comma_Postion_In_String

—without Adjustment

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)) as Substring_On_Comma

—Adjusted to account for extra space

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Substring_On_Comma_Ajusted

Displaying your Netezza query results in a grid can be useful. Especially, when desiring to navigation left and right to see an entire rows data and to avoid the distraction of other rows being displayed on the screen. I use this capability in Aginity when I’m proofing code results and/or validating data in a table.

How To switch to the Single Row Grid

Execute your Query

When the results return, right click on the gray bar above your results (where you see the drag a column box

Choose the ‘Show a Single Row Grid’ Menu item

Aginity Show Single Row Grid

Grid View Change

Your result display will change from a horizontal row to a vertical grid as shown below

Aginity Single Row Grid Display

How to Navigate in the Single Row Grid

To navigate in the single row grid, use the buttons provided at the bottom of the results section.

Adding a forging key to tables in Netezza / PureData is a best practice; especially, when working with dimensionally modeled data warehouse structures and with modern governance, integration (including virtualization), presentation semantics (including reporting, business intelligence and analytics).

Foreign Key (FK) Guidelines

A primary key must be defined on the table and fields (or fields) to which you intend to link the foreign key

Avoid using distribution keys as foreign keys

Foreign Key field should not be nullable

Your foreign key link field(s) must be of the same format(s) (e.g. integer to integer, etc.)

Rebuilding Netezza view sometimes becomes necessary when the view’s source table have changed underneath the view. Rebuilding a view can be done on Netezza or in Aginity. In Aginity, it is a simple process, assume your user has permissions to create or replace a view. The process breaks down into just a few steps:

Generate the create / replace view SQL of the original view into the query window, if you don’t have it already

In the object browser:

Navigate to the Database and view you wish to rebuild

Select the view and right click

Select ‘Scripts’, then ‘DDL to Query window’

Make may updates to create / replace View SQL

This step is not always necessary, sometimes the changes which invalided the view did not actually impact the code of the view. If changes are necessary, make may updates to the SQL code.

Execute The code

This I usually do by choosing the ‘Execute as a single batch’ option. Make sure the code executes successfully.

Verify the view

To verify the simply execute a select statement and make it executes without errors and/or warning.

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.

I had reason today to get the number of the day of the week, in PureData / Netezza, which I don’t seem to have discussed in previous posts. So, here is a simple script to get the number for the day of week with a couple of flavors, which may prove useful.

Basic Format

select extract(dow from <<FieldName>>) from <<SchemaName>>.<<tableName>>

Example SQL

SELECT

CURRENT_DATE

, TO_CHAR(CURRENT_DATE,’DAY’) AS DAY_OF_WEEK

—WEEK STARTS ON MONDAY

, EXTRACT(DOW FROM CURRENT_DATE)-1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_MONDAY

—WEEK STARTS ON SUNDAY

, EXTRACT(DOW FROM CURRENT_DATE) AS DAY_OF_WEEK_NUMBER_STARTS_ON_SUNDAY

—WEEK STARTS ON SATURDAY

, EXTRACT(DOW FROM CURRENT_DATE)+1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_SATURDAY

Aginity for Netezza an out complete feature, which auto completes brackets, quotes, and comment character, which some folks find useful and helpful. However, if you are an old-school style coder like me, you may find these annoying, to say the least. Usually, when I’m coding I want to write straight through without the need to think about how the application may be trying to help me, which means I usually end up cleanup all the autocomplete added characters after the fact. Disabling this feature is quick and easy and can be easily be reinstated later, if you find you want to leverage some or all of the autocomplete features.

Aginity Tools Options

To Disable the Autocomplete Functions

Navigate to: Tools > Options

Then, Navigate to: Query Analyzer > Code autocomplete

Then, Uncheck the feature or features you wish to disable and press, ‘OK’

Disable Aginity Code Autocomplete Options Disable

To Enable the Autocomplete Functions

Navigate to: Tools > Options

Then, Navigate to: Query Analyzer > Code autocomplete

Then, Check the feature or features you wish to enable and press, ‘OK’

The Case Statement, is one which I occasionally use within Netezza/PureData SQL and Stored Procedures. Basically, the Case Function provides an ‘IF-THEN-ELSE’ decision capability. This ‘IF-THEN-ELSE’ capability allows the evaluation conditions and return a value when the first condition is met and/ or else break out logic, if the condition or conditions are not met.