FedSQL Pass-Through Facility :: SAS(R) 9.4 FedSQL Language Reference, Third Edition

Overview

The FedSQL pass-through facility enables you to connect to a data source and send SQL statements directly to that data source for execution. This facility also enables you to use the syntax of your data source, and it supports any non-ANSI standard SQL that is supported by your data source.

You can create a connection to the language processor. This connection supports standard SQL syntax. This connection can also accept native SQL syntax through the use of the CONNECTION TO component of the SELECT statement’s FROM clause, and the EXECUTE statement. Note that on SAS Federation Server, use of these statements is allowed only when the DSN is configured to use personal logins because of security restrictions..

CONNECTION TO Component of the FROM Clause

You should use the CONNECTION TO component of the SELECT statement’s FROM clause to submit native SQL requests that produce a result set.

The CONNECTION TO component has the following syntax:

FROM CONNECTION TO catalog (native-syntax) [[AS] alias]

Arguments

catalog	specifies the name of a catalog in the existing FedSQL connection.
native-syntax	specifies a select-type query (not DDL) to be run on the catalog's driver.
alias	provides a name for the result set produced by the native query.

Example:

select oo.i, oo.rank, ff.onoff
    from connection to catalog_a
        ( select i, rank() over (order by j) rank from table_a ) oo,
    connection to catalog_b
        ( select distinct i, iif(k > 0.5, 1, 0) as onoff from table_a ) ff
    where oo.i = ff.i
order by 1;

For more information, see the SELECT Statement.

EXECUTE Statement

You should use the EXECUTE statement if the native SQL does not produce a result set, such as DML and DDL statements. In addition, the EXECUTE statement accepts native SQL that produces a result set. For more information, see EXECUTE Statement.