Tag Archives: aginity netezza workbench tutorial

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.

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.

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’

Every once and a while, it happens that a SQL needs to be reused, but for whatever reason it was not saved. If it wasn’t too far back in time, you can recover the SQL from the User Query History. This is a fairly straight forward process, if not exactly obvious.

The basic process is:

Connect to the database

Navigate to View > User Query History

Aginity View User Query History

Select the SQL Statement you want to retrieve

note: you want to select a databases and or user to narrow you rows returned.