Changing Access Modes

If you have the SAS/ACCESS interface to ORACLE installed, then you can switch access modes and use the SQL Pass-Through facility to query ORACLE tables.

ORACLE Access Mode Options

From the SQL QUERY TABLES window, select
Tools – Switch Access Mode – ORACLE
to display the ORACLE Access Mode Options window.

Fill in the fields with the information appropriate for your site. Contact your ORACLE administrator for more information.

Select OK to return to the SQL QUERY TABLES window. The sample tables that are available with your ORACLE DBMS are listed in the Available Tables column.

Creating a WHERE Expression

This example shows that although the steps for creating a WHERE expression are the same regardless of access mode, the generated SQL code is specific to the DBMS. If you have the SAS/ACCESS interface to ORACLE installed, then you can follow this example using any ORACLE table.

Select an ORACLE table from the Available Tables list and move it to the Selected Tables list. For this example, ORDERS is used. Select OK to display the SQL QUERY COLUMNS window.

Select one or more columns from the Available Columns list and add them to the Selected Columns list. This example uses FABRICCHARGES, SHIPTO, DATEORDERED, TAKENBY, and PROCESSEDBY.

Select
View – Where Conditions for Subset
to display the WHERE EXPRESSION window.

Create a WHERE expression. For this example, the expression SHIPPED Is Not Missing is created. Note that the Is Not Missing operator is selected from the OTHER Operators list.

Select OK to close the WHERE EXPRESSION window.

Viewing Your Query

From the SQL QUERY COLUMNS window, select
Tools – Show Query
to view your query.

For this example, the query reports information for orders that have been shipped. ORACLE SQL is generated as the query is built.

The syntax that is enclosed by the parentheses that follow from connection to oracle is transported through the SQL Procedure Pass-Through facility to the ORACLE DBMS for processing. The Is Not Missing operator from the WHERE expression is converted to the is not null ORACLE operator.

The syntax that is outside of the parentheses that follow from connection to oracle is processed by SAS.