FedSQL Explicit Pass-Through Facility

The FedSQL explicit 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..

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:

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;

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.