CODEWITHSUNDEEP
db2global-variablestoad
Eliot G York
Eliot G York

Reputation: 161

Toad SQL - Variables

I recent began working in TOAD DB2 and I am trying to design some templates where I use a specific term for multiple queries.

i.e.

SELECT * FROM TABLE1 WHERE PERSON = 'X';
SELECT * FROM TABLE2 WHERE PERSON = 'X';
SELECT * FROM TABLE3 WHERE PERSON = 'X';

and so on...

I was hoping to figure out a way to set a single variable so that I can write the code more like:

SET PERS_CODE = 'X'
SELECT * FROM TABLE1 WHERE PERSON = :PERS_CODE;
SELECT * FROM TABLE2 WHERE PERSON = :PERS_CODE;
SELECT * FROM TABLE3 WHERE PERSON = :PERS_CODE;

and so on...

The difficulty is that I rarely run all the queries as a batch. I run one query to research how to best write code to make BA modifications; research results, and mark down terms that need to change and terms for uniquely identify records. So I run them adhoc and I don't want to run them as a batch or a single procedure.

Instead I am looking for a way to define the variable so that when I run any single query it will find how the variable is defined without needing to put the SET statement into each individual line, without running all the queries as a batch (or single procedure) or using the Project Manager Bind Variables table.

My Research

I have come across the variable definitions table in the "Project Manager", however this is problematic because I will need to remember to change the variable in another screen, for each work item. I want to define the Variables within a single EDITOR window so, I can switch between EDTOR window that use the same variable terminology (but different values) without having to remember to reset the value.

I have also come across the "--TOAD:" syntax that helps define the variable from within the EDITOR window. However this needs to be included in the procedure. Therefore, if I needed to run the query for TABLE2 and not TABLE1, I wouldn't be able to use the variable because the definition would be above the TABLE1 query code.

i.e.

--TOAD: SET PERS_CODE = 'X'
SELECT * FROM TABLE1 WHERE PERSON = :PERS_CODE;
SELECT * FROM TABLE2 WHERE PERSON = :PERS_CODE;

This wouldn't allow me to use the Variable definition for TABLE2 without running the TABLE1 query or modifying the code each time.

Upvotes: 0

Views: 1762

Answers (1)

Michael S.
Michael S.

Reputation: 1791

You may find substitution variables to be more appropriate here. 

SELECT * FROM TABLE1 WHERE PERSON = &&PERS_CODE;
SELECT * FROM TABLE2 WHERE PERSON = &&PERS_CODE;

When you execute one of those statements you are prompted for value of PERS_CODE. When you run multiple statements as script then all will reuse the same value. Substitution variables can be defined using a single ampersand and a double ampersand. When a single ampersand is used you are prompted for each variable in your script even if they have the same name. The double ampersand prompts once and every other occurrence reuses the value. Toad's Editor will remember the last used value so you can continue running queries in an ad hoc manner and you'll only have to reset the value when you want to test a different value.

If PERS_CODE is a string then use it in your query with quotes.

SELECT * FROM TABLE1 WHERE PERSON = '&&PERS_CODE';

EDIT: For performance reasons bind variables are better (:VAR_NAME) but for your needs I think substitution variables will make things easier for you.

Upvotes: 1

Related Questions