| Query
|
An SQL statement used to query the database. The query can be a simple query or a complex query. A complex query has nested SQL statements. Prepared SQL queries can be constructed by using substitution variables (or substitution parameters) of the form
?<fieldname> in the query statement.
For example
select * from student where name like ?name;
Each substitution variable identifies an input parameter whose mapped value will be substituted into the substitution variable at runtime. The substitution variable can be reused for the same input parameter elsewhere in the query. Input parameters used in the WHERE clause, and output parameters used in the SELECT clause, and their corresponding type information is automatically fetched from the database using the selected connection for the entered query. Input and output fields in the
Input and
Output tabs of the activity are also automatically generated.
Note: Be sure to include the semicolon (;) at the end of the query. This activity expects a query to end with a semicolon to indicate the end of the query. A missing semicolon at the end of the query results in the query hanging.
Following are some examples of simple and complex queries:
-
Simple query:
SELECT * FROM employee;
For the above query, the output fields are generated from the table employee's column information.
- Simple query:
SELECT description, url, name FROM webpages WHERE name = ?name and id > ?id ORDER BY name;
For the above query, output fields are generated for
description,
url and
name; and input fields are generated for
name (varchar) and
id (numeric). Also, the mapped values for the fields
name and
id are substituted into the substitution variables
?name and
?id at runtime.
-
Nested query:
SELECT firstname, lastname, total_quantity
FROM (SELECT buyerid, sum(qtysold) total_quantity
FROM sales
GROUP BY buyerid
ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;
|
