| 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. You can construct prepared SQL queries by using substitution variables (or parameters) of the form
?<fieldname> in the query statement. For example,
select firstname, lastname, age from users where firstname='?firstname' and age < '?age';
Each substitution variable identifies an input parameter whose mapped value will be substituted into the substitution variable at runtime. You can reuse the substitution variable 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.
The following examples represent simple and complex queries:
-
Simple query example:
SELECT * FROM users;
For the above query, the output fields are generated from the table user's column information.
SELECT username, firstname, lastname
FROM users
WHERE state = '?state' and likesports = '?likesports' and liketheatre = '?liketheatre' and likejazz = 'true';
For the above query, output fields are generated for
username,
firstname and
lastname and input fields are generated for
state (string),
likesports (boolean),
liketheatre (bool) and
likejazz (bool). Also the mapped values for the fields,
state,
likesports and
liketheatre are substituted into the substitution variables
?state,
?likesports and
?liketheatre at runtime.
-
Nested query example:
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;
|
