Chapter 3 Building Physical Diagrams

Defining a query for a view

You define queries for a view from the SQL Query page of the view property sheet. Each SELECT query is shown in the Query dropdown listbox. The definition for the selected query appears in the textbox.

Selecting tables, views, synonyms, columns, and references for a view

You can add tables, views, synonyms, columns, and references to the view from the property sheet for each query.

Based on your selection of tables, columns, views, synonyms, and references, PowerDesigner generates a query in SQL syntax.

The resulting query uses FROM statements for tables and WHERE statements for columns linked by joins in a reference.

Creating relations between selected tables

Depending on the selected DBMS, you can construct links between tables selected from the Query dropdown listbox. These links allow you to create more complex queries.

Option The resulting query Example
Union Binds two or more SELECT statements. The query result displays all the retrieved data. Repeating query results are not displayed SELECT 1: ABC

SELECT 2: BCD

Result is ABCD
Union All Binds two or more SELECT statements. The query result displays all the retrieved data. Repeating query results are displayed SELECT 1: ABC

SELECT 2: BCD

Result is ABCBCD
Intersect Binds two or more SELECT statements. The query result displays all the retrieved data that is common to the SELECT statements SELECT 1: ABC

SELECT 2: BCD

Result is BC
Minus Binds two or more SELECT statements. The query result displays the difference between all the retrieved data that is common to the consecutive SELECT statements (SELECT 1-SELECT 2-...) SELECT 1: ABC

SELECT 2: BCD

Result is AD

Creating a query with the SQL editor

You can also construct a query using the SQL Editor. The SQL Editor provides a more complete query definition environment than the SQL query page.

In addition to standard SQL statements (SELECT, FROM, WHERE), the SQL Editor offers greater flexibility through syntax tools for functions and operators.

You can open the SQL Editor by clicking the Edit tool in the SQL Query toolbar.


For more information on the SQL Editor, see section Using SQL tools in chapter Physical Data Model Basics.

 

Copyright (C) 2005. Sybase Inc. All rights reserved.