How do I write a procedure that gets the values of two columns (e.g. CompanyName, City) from a table?
Article ID: KB0082285
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | v 7.0.0 and higher |
Description
The procedure will first need to run a query on the table, then run a fetch statement to retrieve the values of the two columns from the query results.
Below is a sample procedure that does this. It runs a query on a table named Customers, and then runs a fetch to retrieve the values of columns CompanyName and City from the query results. It stores the values in variables called 'name' and 'city'.
PROCEDURE pReadTwoColumns()
BEGIN
declare result cursor;
declare name VARCHAR(100);
declare city VARCHAR(100);
OPEN result FOR SELECT CompanyName, City from /shared/examples/ds_orders/tutorial/Customers;
fetch result into name, city;
whileloop:
while result.found
do
begin
fetch result into name, city;
CALL PRINT ('The company name is : ' || name || ' and the city is : ' || city );
end;
end while;
close result;
END
The Studio Console displays the following output:
The company name is : Anston Systems and the city is : Burlingame
The company name is : Blackard Electronics and the city is : San Mateo
The company name is : Connect Software and the city is : Philadelphia
The company name is : Canal Street Solutions and the city is : Reading
Below is a sample procedure that does this. It runs a query on a table named Customers, and then runs a fetch to retrieve the values of columns CompanyName and City from the query results. It stores the values in variables called 'name' and 'city'.
PROCEDURE pReadTwoColumns()
BEGIN
declare result cursor;
declare name VARCHAR(100);
declare city VARCHAR(100);
OPEN result FOR SELECT CompanyName, City from /shared/examples/ds_orders/tutorial/Customers;
fetch result into name, city;
whileloop:
while result.found
do
begin
fetch result into name, city;
CALL PRINT ('The company name is : ' || name || ' and the city is : ' || city );
end;
end while;
close result;
END
The Studio Console displays the following output:
The company name is : Anston Systems and the city is : Burlingame
The company name is : Blackard Electronics and the city is : San Mateo
The company name is : Connect Software and the city is : Philadelphia
The company name is : Canal Street Solutions and the city is : Reading
Issue/Introduction
How do I write a procedure that gets the values of two columns (e.g. CompanyName, City) from a table?
Feedback
Yes
No
Powered by
