Tools The following table lists the Visionary Studio tools you can use to create and modify data sources.
Simple Query wizard
Guides you through the steps to create an SQL query from a single table and, optionally, to specify aggregates and functional expressions.
Creating an SQL query from a single table.
Advanced Query wizard
Guides you through the steps to create an SQL query from multiple tables in the database and, optionally, to specify filtering criteria, query parameters, aggregates, and functional expressions.
Creating a complex SQL query.
Query Editor
Provides a design window with the following views:
Query Diagram. Provides a design window in which you can modify and compose an SQL query graphically. This option requires some familiarity with SQL.
SQL Text. Provides a text window in which you can view, modify, and compose SQL queries. This option offers the most flexibility but requires fluency in SQL.
Data Sheet. Provides a window in which you can view returned data from an SQL query.
Modifying, viewing, testing, and composing SQL queries.
Query Design panel in the World Manager
Contains a palette of query blocks you can use to create and modify SQL queries.
Modifying an SQL query.
Database Contents panel in the World Manager
Contains a tree view of the tables and views, functions, data types, aggregates, and procedures in the active database.
Viewing database contents to compose and modifying queries.
Using the Query Wizards Visionary Studio provides Simple Query and Advanced Query wizards that enable you to compose queries with a minimal knowledge of SQL. You can launch the query wizards in one of the following ways:
1. Insert a data source.
2. Launch the query wizard.
3. Select a single table or view and columns that contain the data you want to present.
4. Define functional expressions, define aggregates, add multiple tables, and add parameters for your data source. You can only add multiple tables and parameters using the Advanced Query wizard.
5. Define query output.
6. Execute and view the query results in the data sheet.
Simple Query Wizard Use the Simple Query wizard to query a single table in the database. The number of steps in the Simple Query wizard depends on whether or not you want to define functional expressions or aggregate functions in your query. The following example shows the general format of a query generated by the Simple Query wizard:
1. Choose Insert DataSource to display the New Data Source dialog box
Figure 3-1 New Data Source Dialog Box
2. Select Simple Query Wizard and click OK to launch the wizard.
3. Select a table or view and the columns of data you want to display.
Figure 3-2 Selecting a Table and Columns
4. If you want to define a functional expression for your query, double-click (Expression) in the Available fields list box to display the Expression Editor dialog box.
Figure 3-3 Expression Editor Dialog Box
Function button
5. Click the Function button in the Expression toolbar to display the Choose Function dialog box.
6. Select the function type, name, and version, and then click OK to return to the Expression Editor. A General function returns a calculated column; a Filter function returns a True or False value based on restriction criteria.
Figure 3-4 Choosing a Function
Column button
7. Highlight the function argument and click the Column button on the Expression toolbar to display the Choose Column dialog box.
Figure 3-5 Selecting the Function Argument
8. Select a column from the Column name list box and click OK to return to the Expression Editor.
Figure 3-6 Choosing a Column
Figure 3-7 Expression Editor with a Defined Functional Expression
Cancel and Apply buttons
9. Click OK in the Expression Editor to return to the query wizard.
10. Click Next to display the Summarize Records page of the query wizard.
Figure 3-8 Summarizing Records Page
11. Select the column you want to summarize and the aggregate function; then click Add. For example, to return the total shipping charges, you would select the orders.ship_charge column and the Sum() function.
Figure 3-9 Defining an Aggregate in a Query
12. Click Next to display the Output page of the query wizard.
13. To order the output of your query, type the query name and field names, specify output order and sort keys, and then click Finish.
Figure 3-10 Naming Your Query and Ordering Output
14. Click Yes in the Visionary Studio prompt to execute and view your query results.
Figure 3-11 Viewing Query Results in the Data Sheet
Figure 3-12 New Data Source Dialog Box
2. Select Advanced Query Wizard and click OK to launch the wizard.
3. Select a single table or view and columns of data you want to display.
Figure 3-13 Selecting a Table and Columns
4. To define functional expressions in your query, double-click (Expression) or click Edit to display the Expression Editor. For detailed instructions on defining expressions using the Expression Editor, see the Simple Query wizard example on page 3-9.
5. Optionally, click Yes, there is more information and Next to query additional tables in the database.
Figure 3-14 Querying Additional Tables
6. Select the additional table and columns and click Next. The tables must be related in some way; for example, by a matching column name or a calculated expression:
Figure 3-15 Selecting an Additional Table and Columns
7. To define the relationship between the two tables, click the Fields from <tablename> cell and the Column button in the Expression Editor toolbar to display the Choose Column dialog box.
Figure 3-16 Choosing a Column
8. Select a column and click OK to return to the query wizard.
9. Click the Apply button in the Expression toolbar to display the column name in the Related fields <tablename> cell.
Figure 3-17 Defining Column Relationships
10. Click the Operator cell and the Function button in the Expression toolbar to display the Choose Boolean Function dialog box.
Figure 3-18 Choosing a Boolean Operator
11. Select an operator and click OK to return to the query wizard. Apply button
Apply button
12. Click the Apply button in the Expression toolbar to display the operator in the Operator cell.
Figure 3-19 Defining a Boolean Operator
13. To select the field that relates to the field in the first table, click the Related field cell and the Column button in the Expression toolbar to display the Choose Column dialog box.
Figure 3-20 Choosing a Related Field Column
14. Select a column and click OK to return to the query wizard. Apply button
15. Click the Apply button in the Expression toolbar to apply the relationship you have defined.
Figure 3-21 Defining a Related Column
16. Click Next to display the Additional Tables page of the query wizard. If you want to add more tables to your query, repeat the steps on pages 3-17 through 3-21.
17. If you do not want to include additional tables, click No, there is no more information to be added and click Next.
Figure 3-22 Additional Tables Page
18. If you want to filter your query results, click Yes, filter the rows based on additional criteria and click Next to specify how you want to filter data. You can specify expressions based on defined parameters, functions, or user-defined expressions
Figure 3-23 Filter Rows Page
19. Type an expression in the Expression text box to specify the search criteria for the rows in the table. For example, to return only those items where the total price is greater than or equal to $250, you would type items.total_price >= 250 in the Expression text box. In addition, when you define constants in an expression they must be enclosed in single quotation marks, for example, customer_name = 'Informix'.
items.total_price >= 250
customer_name = 'Informix'
Figure 3-24 Defining Search Criteria
20. To define a query parameter, click Define Parameter to display the Query Parameter dialog box. A query parameter is a value assigned to a variable.
21. Define the parameter and click Set and OK to return to the query wizard. For detailed instructions on defining query parameters using the Query Parameter dialog box, see "Inserting Query Parameters"
Figure 3-25 Defining a Query Parameter
22. Type the filter criteria in the Expression text box. You combine filter constraints using the logical operators AND and OR.
Figure 3-26 Combining Search Constraints
23. If you want to define aggregate functions for your query, click Summarize the records and click Next. An aggregate is a function that summarizes information about the groups of rows in a table. If you do not want to summarize the query results, click Return individual records. For detailed instructions on defining aggregate functions, see the Simple Query wizard example on page 3-11.
Figure 3-27 Returning Individual Records
24. To order the output of your query, type the query name and field names, specify output order and sort keys, and then click Finish.
Figure 3-28 Naming Your Query and Ordering Output
25. Click Yes in the Visionary Studio prompt to execute and view the query results.
Figure 3-29 Viewing Results in the Data Sheet
Figure 3-30 Query Editor
There are two types of query blocks: data and operation. The following sections first describe these two types, and then describe ports and connections. Data Blocks Data blocks represent the data you want to query. Data blocks include the following types:
i n.<identifier>
n
*)
columname
sv
1. Insert a data source and select Query Diagram.
2. Insert query blocks.
3. Select a table or view for your query.
4. Define query properties such as filters, parameters, and constants, as desired.
5. Define the column output.
1. Choose Insert Data Source to display the New Data Source dialog box.
2. Select Query Diagram View from the list box and click OK.
3. Double-click the Table, Constant, Filter, and Output blocks to place them in the Query Diagram view.
4. Click the Input and Output ports to connect the query blocks.
Figure 3-31 Connecting Query Blocks
5. Right-click the Table block and select Properties to display the Table dialog box.
6. Select a table or view from the Table name list box and click OK.
Figure 3-32 Selecting a Table or View
7. Right-click the Constant block and select Properties to display the Constant dialog box.
8. Define the constant value. Text values must be enclosed in single quotation marks, for example 'Sunnyvale'. If necessary, click Cast value as type and select the data type value from the list box. The data type is used to cast the value of the parameter in the SQL query before it is submitted to the database. Click OK to return to the Query Editor.
'Sunnyvale'
Figure 3-33 Defining a Constant
9. Right-click the Filter block and select Properties to display the Filter dialog box.
Figure 3-34 Defining Filter Criteria
10. To define a single argument, click Standard operator and select a filter expression from the Filter Expression box; then click OK. You can define two argument functional expressions by selecting the Two argument function option.You can also change the column placement and define a NOT clause by checking the Place column on the right and Negate comparison check boxes, respectively.
Figure 3-35 Defining Filter Properties
11. Right-click the Output block and select Properties to display the Output dialog box.
12. Select columns from the Available fields list and click OK.
Figure 3-36 Defining and Ordering Output
13. Click SQL Text to view the generated SQL expression. If the query is not correctly defined, a query does not appear. The Output window displays execution and error messages.
Figure 3-37 SQL Text View
14. Click Data Sheet to view the results of your query. Execution and error messages appear in the Output window.
Figure 3-38 Data Sheet View
Data Source
