[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL script passing column names as a variable in a query

Posted on 2012-08-17
10
Medium Priority
?
3,993 Views
Last Modified: 2012-09-10
Hi Experts,

I'm using Netezza SQL (which I think it's an ANSI SQL standard)  to run the query below:

SELECT DISTINCT location_nk
	FROM LRST_DB.ELT_SERVICE.VW_LRST_IT_ON_LLT_RDS

Open in new window


Rather than have 20 of this query I was wondering if it was posible to just pass the column name as a variable to the query so that I can check distinct for each column.

Thanks,

OS
0
Comment
Question by:onesegun
  • 6
  • 3
10 Comments
 
LVL 16

Expert Comment

by:kmslogic
ID: 38307184
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
0
 
LVL 35

Accepted Solution

by:
johnsone earned 150 total points
ID: 38318147
What tool are you using to connect to the database?

You can do it in nzsql without creating a stored procedure.

In your example, you would do something like this:

nzsql -d dbname -u user -pw password -c "SELECT DISTINCT :cname FROM LRST_DB.ELT_SERVICE.VW_LRST_IT_ON_LLT_RDS" -v cname=location_nk

Then all you have to do is change the column name on the end of that command line and it will use a different column.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38318200
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

Open 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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:onesegun
ID: 38319745
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
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38320378
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.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38320498
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
0
 

Author Comment

by:onesegun
ID: 38320682
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
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38321107
The pop up is how aginity gets the value for the parameter.  How do you want to pass the parameter?
0
 

Author Comment

by:onesegun
ID: 38321137
I was hoping I could have it within the query so there is no manuall intervention?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38321218
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?
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question