Run Netezza SQL Script File using nzsql Variable Substitution

  • Post author:
  • Post last modified:March 29, 2019
  • Post category:Netezza
  • Reading time:3 mins read

Netezza allows the variable substitution using -v option in Netezza nzsql queries. The variable substitution is very important when you are calling the Netezza nzsql scripts from shell or any other scripting language. You can pass the values to query that you are calling using -v option.

Run Netezza SQL Script File using nzsql Variable Substitution

In this article, we will see how to run Netezza nzsql script file using nzsql variable substitution. We also see some of the working examples that uses the Netezza variable substitution method.

Run Netezza SQL Script File using nzsql Variable Substitution

You can set and use that variable within the script.

Netezza nzsql supports variable substitution using -v option. Below is the sample syntax that you can use to run Netezza nzsql script file using nzsql variable substitution.

[nz@netezza~]$ nzsql -db training -u username -pw password -host netezza_host -v name=val -f '/path/sample_script.sql'

Above command is the sample example to set nzsql variable ‘name’ to value ‘val’.

Run Netezza SQL Script File using nzsql Variable Substitution Example

Below is the working example to run Netezza SQL script file using nzsql variable substitution:

[nz@netezza ~]$ cat /export/home/nz/sample-script.sql
select * from PATIENT_1
where ID = :name
;

[nz@netezza~]$ nzsql -db training -v name=7 -e -f '/export/home/nz/sample-script.sql'
select * from PATIENT_1
where ID = 7
 ID| NAME 
----+------
 7| BBB
(1 row)

You can use the any scripting program to dynamically pass the parameter to the Netezza SQL script file.

Read:

Tags: ,