SQL script passing column names as a variable in a query

It looks like you'll have to create a stored procedure to do this.  Here's an example:

http://datawarehouse.ittoolbox.com/groups/technical-functional/netezza-l/netezza-variable-declaration-3297018

If you wanted to go crazy, here is the beginning of a shell script that would go through every column in the table and give you the distinct values for it.

db=$1
tab=$2
user=$3
pass=$4
for col in `nzsql -A -t -d $db -u $user -pw $pass -c "select attname from _t_object a, _t_attribute b where a.objid = b.attrelid and upper(a.objname) = upper('$tab') and a.objdb = current_db and b.attnum > 0 order by b.attnum"`
do
  echo "========== Distinct values for column $col =========="
  nzsql -A -t -d $db -u $user -pw $pass -c "SELECT DISTINCT :cname FROM $tab" -v cname=$col
done

Select allOpen in new window

You do need to be able to connect to the database and query the underlying tables, so it could require some privileges, but you might be able to get it to work.  It is just showing you a framework of what you could do.

Hi Johnsone,

I'm using Aginity Workbench for Netezza to connect.

The first script throws up an error:

error    ^ found "NZSQL" (at char 1) expecting a keyword

Thanks,

OS

That will not work with Aginity.  nzsql is a command line tool similar to SQL*Plus in an Oracle installation.  You can download a client package for Netezza that includes that program.

I am not that familiar with Aginity.  However, the procedure method should work fine.  I was just trying to give another way to do it without having to create a procedure.

From the help text, it looks like this will do what you want in Aginity:

http://www.aginity.com/Workbench/WorkbenchforNetezza/MenuBar/QueryMenu.aspx#paramquery

Hi Johnsone,

I had already played with the parameters features of Aginity and it works fine. Only thing is you have to enter the parameters into this pop-up box for a different view or column name every time you want to change :-( And I don't think Netezza SQL supports passing of parameters...

Thanks,

OS

The pop up is how aginity gets the value for the parameter.  How do you want to pass the parameter?

I was hoping I could have it within the query so there is no manuall intervention?

Can you show an example of how you would pass the parameter with no manual intervention?  Maybe we can find a way to do what you want, but no matter how you look at it, someone (or something) needs to supply the value to the parameter.

I realize the query should look something like this:

SELECT DISTINCT :cname FROM LRST_DB.ELT_SERVICE.VW_LRST_IT_ON_LLT_RDS

But how will the value for the cname parameter get through to the query?  Where is it coming from?