SQL Statements and Extensions for the Salesforce Driver : Create View

Create View
Purpose
The Create View statement creates a new view. A view is analogous to a named query. The view's query can refer to any combination of remote and local tables as well as other views. Views are read-only; they cannot be updated.
Syntax
CREATE VIEW view_name[(view_column,...)] AS
SELECT ... FROM ... [WHERE Expression]
[ORDER BY order_expression [, ...]]
[LIMIT limit [OFFSET offset]];
where:
view_name
Specifies the name of the view.
view_column
Specifies the column associated with the view. Multiple column names must be separated by commas.
The other commands used for Create View are the same as those used for Select (see Select).
Notes
Example A
This example creates a view named myOpportunities that selects data from three database tables to present a virtual table of data.
CREATE VIEW myOpportunities AS
SELECT a.name AS AccountName,
o.name AS OpportunityName,
o.amount AS Amount,
o.description AS Description
FROM Opportunity o INNER JOIN Account a
ON o.AccountId = a.id
INNER JOIN User u
ON o.OwnerId = u.id
WHERE u.name = 'MyName'
AND o.isClosed = 'false'
ORDER BY Amount desc
You can then refer to the myOpportunities view in statements just as you would refer to a table. For example:
SELECT * FROM myOpportunities;
Example B
The myOpportunities view contains a detailed description for each opportunity, which may not be needed when only a summary is required. A view can be built that selects only specific myOpportunities columns as shown in the following example:
CREATE VIEW myOpps_NoDesc as
SELECT AccountName,
OpportunityName,
Amount
FROM myOpportunities
The view selects the name column from both the opportunity and account tables. These columns are assigned the alias OpportunityName and AccountName, respectively.

© 2013 Progress Software Corporation and/or its subsidiaries or affiliates.