Netezza / PureData – How To Quote a Single Quote in Netezza SQL

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.

Example SQL:

SELECT Distinct

‘,’||””|| S1.ORDER_NUM||”” as Quoted_Order_Number

FROM Sales S1

 

How to Quote A Single Quote Example SQL

How to Quote A Single Quote Example SQL

Related Reference

Aginity for Netezza – How to disable and/or Enable the Autocomplete Function

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

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

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
Enable Aginity Code Autocomplete Options Disable

Enable Aginity Code Autocomplete Options Disable

Related References

PureData / Netezza – Useful links

IBM Netezza / IBM PureData for Analytics Useful links

IBM Netezza / IBM PureData for Analytics

Here are a few Netezza references, which I have found useful.

Reference Type Link
Vendor Documentation https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.0/com.ibm.nz.welcome.doc/doc/welcome.html
IBM Developer Works https://www.ibm.com/developerworks/community/groups/service/html/communitystart?communityUuid=9c8f1300-9ac0-4de5-80e6-0
Aginity Workbench for Netezza http://www.aginity.com/workbench/netezza/
IBM Netezza Database User’s Guide https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.0/com.ibm.nz.dbu.doc/c_dbuser_ntz_sql_introduction.html
Client Software Packages https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.0/com.ibm.nz.adm.doc/c_sysadm_client_software_packages.html
Netezza Client Software Installation https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.0/com.ibm.nz.adm.doc/c_sysadm_client_software_install.html

Related References