Hybrid Queries (from Fraktal SAS Programming): Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
K |
K |
||
(12 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt) | |||
Zeile 1: | Zeile 1: | ||
− | [[Kategorie: | + | [[Kategorie:zazy]] |
+ | {{SeitenNavigation1 | ||
+ | |links=xx_left.png | ||
+ | |zurück=Libname Engine (from Fraktal SAS Programming) | ||
+ | |rechts=xx_right.png | ||
+ | |vorwärts=Passthru SQL (from Fraktal SAS Programming) | ||
+ | |hoch=Duck_zazy_com.png | ||
+ | |übersicht=DBMS Interaction (from Fraktal SAS Programming) | ||
+ | }} | ||
− | + | == What is this? == | |
− | + | The qualifying term ''"hybrid"'' denotes to the fact that a single piece of SQL code is processed by a number of systems that are integrated by appropriate middleware in a way that system borders are not visible when looking at the code. | |
− | + | We will use a maximum simplified task to be coded in SQL: | |
+ | * A '''SAS dataset''' shall be created | ||
+ | * Data for which are kept in a '''DBMS table''' | ||
+ | * The DBMS talks '''ANSI SQL''' | ||
+ | * SAS can connect to the DBMS using '''SAS/Connect software''' | ||
+ | * The solution uses '''Proc SQL''' from Base SAS software | ||
− | |||
− | + | == Documented Code Example == | |
+ | {| class="wikitable" | ||
+ | |- | ||
+ | ! Code executed | ||
+ | ! Function performed | ||
+ | |- | ||
+ | | | ||
+ | proc sql; | ||
+ | | '''Start the SQL interpreter inside SAS''' | ||
+ | |- | ||
+ | | | ||
+ | connect to oracle | ||
+ | (user="&DB_USR." | ||
+ | password="&DB_PWD." | ||
+ | path="&DB_PTH." | ||
+ | ); | ||
+ | | '''Open connection to the DBMS using your credentials stored in [[Symbol_Tables_(from_Fraktal_SAS_Programming)|Macro Variables]]''' | ||
+ | |- | ||
+ | | | ||
+ | create table my_sas_tab as | ||
+ | | '''Initiate creating your SAS table using ANSI SQL code''' | ||
+ | |- | ||
+ | | | ||
select * | select * | ||
from connection to oracle | from connection to oracle | ||
− | + | | '''Start your query from inside SAS with a special from clause''' | |
− | + | |- | |
− | + | | | |
( | ( | ||
select * | select * | ||
from ops$&DB_USR..&MYTABLE. | from ops$&DB_USR..&MYTABLE. | ||
) | ) | ||
− | + | | '''Inside brackets use DBMS specific SQL; [[Symbol_Tables_(from_Fraktal_SAS_Programming)|Macro Variables]] are resolved before code is passed to any processing system''' | |
− | ; | + | |- |
− | + | | | |
; | ; | ||
+ | | '''Execute the query by issuing the SAS statement terminator ';'''' | ||
+ | |- | ||
+ | | | ||
+ | disconnect from oracle; | ||
+ | | '''Close connection to DBMS''' | ||
+ | |- | ||
+ | | | ||
+ | quit; | ||
+ | | '''Terminate SAS SQL interpreter status''' | ||
+ | |} | ||
− | + | {{SeitenNavigation1 | |
− | + | |links=xx_left.png | |
− | + | |zurück=Libname Engine (from Fraktal SAS Programming) | |
− | + | |rechts=xx_right.png | |
− | + | |vorwärts=Passthru SQL (from Fraktal SAS Programming) | |
− | + | |hoch=Duck_zazy_com.png | |
− | + | |übersicht=DBMS Interaction (from Fraktal SAS Programming) | |
+ | }} |
Aktuelle Version vom 2. Juli 2014, 14:32 Uhr
What is this?
The qualifying term "hybrid" denotes to the fact that a single piece of SQL code is processed by a number of systems that are integrated by appropriate middleware in a way that system borders are not visible when looking at the code.
We will use a maximum simplified task to be coded in SQL:
- A SAS dataset shall be created
- Data for which are kept in a DBMS table
- The DBMS talks ANSI SQL
- SAS can connect to the DBMS using SAS/Connect software
- The solution uses Proc SQL from Base SAS software
Documented Code Example
Code executed | Function performed |
---|---|
proc sql; |
Start the SQL interpreter inside SAS |
connect to oracle (user="&DB_USR." password="&DB_PWD." path="&DB_PTH." ); |
Open connection to the DBMS using your credentials stored in Macro Variables |
create table my_sas_tab as |
Initiate creating your SAS table using ANSI SQL code |
select * from connection to oracle |
Start your query from inside SAS with a special from clause |
( select * from ops$&DB_USR..&MYTABLE. ) |
Inside brackets use DBMS specific SQL; Macro Variables are resolved before code is passed to any processing system |
; |
Execute the query by issuing the SAS statement terminator ';' |
disconnect from oracle; |
Close connection to DBMS |
quit; |
Terminate SAS SQL interpreter status |