Doing More with SAS/ASSIST Software

Creating Template SQL Programs

Many times you might want to begin a new program by using part of another program. Or, you might want to begin a new program by using the tools that Query and Reporting offers to create a template program. The following sections show you how to use these tools to create a template that you can edit and customize for your purposes.

Using a Stored Query or Statement

You can include a query that you have previously created in the Query window or include a query or non-SELECT statement that you previously created in the SQL Editor window. From the SQL Editor window, select Open from the File menu. The Open window appears with a list of your saved queries or non-SELECT statements (of types QUERY and SQL). Type an S (for Select) next to the query or statement that you want to include.

The Open window has the fields shown in the following table.

Fields in the Open Window
Field Description

Catalog
Specifies the current catalog name. To change it, type a two-level name or ? to list all possible catalogs. Then make a selection from the resulting Select a Member window.

Name
At the top of the display, the Name field is empty, by default. To search for and limit the listing to certain entries, type the complete name of an entry, or type a portion of a name with a pattern-matching character (such as % or _). For example, specifying JOB% retrieves all of the queries that begin with JOB. Type % for a list of all the saved entries.

Cmd
Type S or an asterisk ( *) to select the query or non-SELECT statement.

Row
Displays the number of queries and non-SELECT statements. You can edit the value of the starting number to change the number of the rows displayed.

Name
In the window's listing, Name displays the names of your queries or non-SELECT statements.

Description
Describes your queries or non-SELECT statements.

Type
Lists whether the entry was saved from the SQL Editor window (SQL) or from the Query window (QUERY).

Date
Lists the date when the query or non-SELECT statement was created or last edited.

***Fields in the Open Window***
Field	Description
`Catalog`	Specifies the current catalog name. To change it, type a two-level name or `?` to list all possible catalogs. Then make a selection from the resulting Select a Member window.
Name	At the top of the display, the Name field is empty, by default. To search for and limit the listing to certain entries, type the complete name of an entry, or type a portion of a name with a pattern-matching character (such as `%` or `_`). For example, specifying `JOB%` retrieves all of the queries that begin with `JOB`. Type `%` for a list of all the saved entries.
Cmd	Type `S` or an asterisk ( `*`) to select the query or non-SELECT statement.
Row	Displays the number of queries and non-SELECT statements. You can edit the value of the starting number to change the number of the rows displayed.
Name	In the window's listing, Name displays the names of your queries or non-SELECT statements.
Description	Describes your queries or non-SELECT statements.
Type	Lists whether the entry was saved from the SQL Editor window (SQL) or from the Query window (QUERY).
Date	Lists the date when the query or non-SELECT statement was created or last edited.

You can also sort the contents of the window by Name, Description, Date, or Type by selecting Sort By from the View menu.

After you type S in the Cmd field and press ENTER, the query or non-SELECT statement is loaded into the SQL Editor window and a message is displayed to indicate that the query has been loaded. Select Goback to return to the SQL Editor window.

If you load a query of type QUERY, changes that you make to your SQL code in the SQL Editor window are not reflected in the query that you display with the Query window. However, you can save your changed SQL code, as described in Saving SQL Code or Output.

You can now edit or run your SQL code. You can edit it in the SQL Editor window or select Enhanced Editor from the Edit menu. The Enhanced Editor enables you to make more significant changes to your code. See Using the Enhanced SQL Editor for more information.

Selecting Tables with the Select Window

You can use the Query and Reporting Select window to include tables in the SQL Editor. Select Query Manager from the Tools menu to open the Query and Reporting Select window. Choose one or more tables and columns, as described in Selecting Tables and Columns for Queries.

Note: Selections made from the Query and Reporting Select window replace non-SELECT SQL statements in the SQL Editor window and append SQL queries in the SQL Query window. If you want to append table selections to non-SELECT SQL code, or want to replace SQL queries, do not use the Query and Reporting Select window; instead, use the SQL Select Table window as described in the next section. [cautionend]

After you select your columns and select Close from the File menu, you return automatically to the SQL Editor window. SQL code that is based on your selections is displayed in the SQL Editor window.

Under DB2 SQL, if a column name has special or national characters (such as FLIGHT#), the column automatically appears in quotation marks.

If relations are defined on the tables, the tables are joined automatically; a WHERE clause is automatically generated in the SQL code. If relations are not defined, edit the SQL code to add a WHERE clause to complete the join.

Choosing Tables with Select Table

Another way to begin a template program is to follow these instructions:

Choose Select Table from the Edit menu. The SQL Select Table window appears.

SQL Select Table Window
Type the two-level name of a table that you want as the basis of your query.
Select Append to append the table to the query currently in the SQL Editor window, or select Replace to replace it.
Select OK. The SQL template program appears in the SQL Editor window. In this example, all the columns are selected from the AIRLINE.DELAY table, as shown in the following display. Table and column aliases, formats, labels, and lengths are automatically generated for the table(s) that you include.

SQL Editor Window With AIRLINE.DELAY Table Selected
To join the AIRLINE.DELAY with the AIRLINE.FLINFO table, choose Select Table again and type the table name AIRLINE.FLINFO. Select Append to append the two tables. Select OK. If relations are defined on the tables, a WHERE clause is automatically generated in the SQL code and the join is complete.

In this example, no relations are defined on the AIRLINE.DELAY and AIRLINE.FLINFO tables. For a join to work efficiently, it must have a WHERE (or ON) clause, and so you must edit the SQL code.

You can do minor editing in the SQL Editor window by using the Edit menu's Insert Line and Delete Line items. For more substantial editing, use the SQL Enhanced Editor, as described in the following section.

Note: If you decide that you do not want to join AIRLINE.DELAY with AIRLINE.FLINFO and instead want only AIRLINE.FLINFO, choose Select Table again. Type the name AIRLINE.DELAY and select Replace. The SQL template program for the join is then replaced by a template program for AIRLINE.FLINFO. [cautionend]

Chapter Contents
Previous
Next
Top of Page